AGGREGATE

Run one of 19 built-in calculations on a range while skipping hidden rows, error values, or nested subtotals.

Math & Trigonometry
|
Excel 2010+
|
Google Sheets Not supported

Spreadsheet editor

Syntax

=AGGREGATE(function_num, options, ref1, [ref2], ...) Returns: Number

Arguments

Argument Required Description
function_num Yes A number from 1 to 19 that selects which calculation to run: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV.S, 8=STDEV.P, 9=SUM, 10=VAR.S, 11=VAR.P, 12=MEDIAN, 13=MODE.SNGL, 14=LARGE, 15=SMALL, 16=PERCENTILE.INC, 17=QUARTILE.INC, 18=PERCENTILE.EXC, 19=QUARTILE.EXC.
options Yes A number from 0 to 7 that controls what to skip: 0=ignore nested SUBTOTAL and AGGREGATE results, 1=ignore hidden rows and nested SUBTOTAL and AGGREGATE results, 2=ignore errors and nested SUBTOTAL and AGGREGATE results, 3=ignore hidden rows, errors, and nested SUBTOTAL and AGGREGATE results, 4=ignore nothing, 5=ignore hidden rows, 6=ignore errors, 7=ignore hidden rows and errors.
ref1 Yes The range or array to calculate. For functions 14-19 (LARGE, SMALL, PERCENTILE, QUARTILE), this is the data array.
ref2 No A second range for the reference form, or the k value for functions that need a rank or percentile position (LARGE, SMALL, PERCENTILE, QUARTILE).

About

AGGREGATE runs a calculation you choose from a list of 19 operations on a range of data. The function_num picks which one to use (9 for SUM, 1 for AVERAGE, 4 for MAX, and so on up to 19 for QUARTILE.EXC). The options value controls what to skip during the calculation: hidden rows, error values like #N/A or #DIV/0!, or nested SUBTOTAL and AGGREGATE functions. In practice, that means if your range includes a cell that already contains =SUBTOTAL(...) or =AGGREGATE(...), options 0-3 can ignore that cell so you do not count a summary result twice.

Use AGGREGATE when a regular SUM or AVERAGE would break because your data contains errors, or when you want a result that only counts visible rows after filtering. It handles both scenarios where SUBTOTAL falls short: error skipping and access to statistical functions like LARGE, SMALL, MEDIAN, and PERCENTILE.

Note that functions 14 through 19 (LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC) require a second argument, k, for their rank or percentile position. Also, AGGREGATE is designed for vertical ranges (columns), not horizontal ones (rows).

Examples

SUM fails with errors, AGGREGATE does not

See what happens when SUM encounters an error value. Cell B4 returns #N/A from a failed lookup, and SUM breaks. Watch how AGGREGATE(9, 6, ...) handles the same range without missing a beat.

Spreadsheet editor

Six different stats from one column

Try changing the first argument in any formula in column C. Swap 9 for 1, 4, 5, 2, or 12 and watch it switch between Sum, Average, Max, Min, Count, and Median. All six skip the error in A4 automatically.

Spreadsheet editor

Top-N rankings with LARGE (function 14)

Use AGGREGATE with function_num 14 to pull the top three sales values from the list. The last argument sets the rank. Change 3 to 4 or 5 to go further down. This is LARGE functionality that SUBTOTAL can't do.

Spreadsheet editor

Watch out for

⚠

AGGREGATE is for columns, not rows

Passing a horizontal range like A1:Z1 gives unreliable results. AGGREGATE is designed to work on vertical data (columns).

→ Structure your data in columns and pass a vertical range like A1:A100. If your data is in rows, transpose it first.

⚠

Missing k for LARGE, SMALL, and PERCENTILE functions

Using function_num 14 (LARGE), 15 (SMALL), 16-19 (PERCENTILE and QUARTILE variants) without a second argument returns a #VALUE! error.

→ Add the rank or percentile as the last argument. For example, =AGGREGATE(14, 5, A1:A100, 3) finds the 3rd largest value. The k argument always goes at the end.

⚠

Options 0-3 silently skip nested SUBTOTAL results

If your range contains cells with SUBTOTAL formulas, options 0 through 3 ignore them by default. This can cause your total to be lower than expected.

→ Use options 4-7 if you want nested SUBTOTAL or AGGREGATE results included in the calculation. Option 4 ignores nothing at all.

⚠

Confusing options values for similar behaviors

Options 1-3 and 5-7 look similar but differ in whether they also skip nested SUBTOTAL/AGGREGATE functions. Getting this wrong leads to subtly incorrect results.

→ Quick reference: use 5 to skip hidden rows only, 6 to skip errors only, 7 to skip both (but still include nested formulas). Use 1, 2, or 3 when you also want nested subtotals excluded.

Tips & notes

Functions 12-19 use the array form of AGGREGATE, where the third argument is a range and the optional fourth argument is k. Functions 1-11 use the reference form and can accept multiple separate ranges (ref1, ref2, ...). The function does not work on 3D references across multiple sheets.

Common questions

How is AGGREGATE different from SUBTOTAL?

AGGREGATE supports 19 operations versus SUBTOTAL's 11. The key additions are MEDIAN, MODE.SNGL, LARGE, SMALL, and the PERCENTILE and QUARTILE variants. AGGREGATE also lets you skip error values, which SUBTOTAL cannot do. For basic filtered-list calculations, SUBTOTAL works fine. When you need statistical functions or error handling, use AGGREGATE.

Can I use AGGREGATE to sum only visible rows?

Yes. Use function_num 9 (SUM) with options 5 (ignore hidden rows): =AGGREGATE(9, 5, A2:A100). The result updates automatically when you filter or hide rows. Options 1 also ignores hidden rows but additionally skips nested SUBTOTAL and AGGREGATE results.

Why does my AGGREGATE formula return #VALUE!?

The most common cause is using a function that requires a k argument (function_num 14-19: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC) without providing that second reference. Add the rank or quartile number as the last argument in the formula.

Does AGGREGATE work in Google Sheets?

No. AGGREGATE is an Excel-only function. In Google Sheets, you can combine individual functions like LARGE, AVERAGE, or PERCENTILE with FILTER to get similar results for visible rows.