AGGREGATE function
AGGREGATE works like SUBTOTAL but can also ignore error values, so it can total or rank a column that contains #N/A or #DIV/0!. It also covers percentiles, quartiles, LARGE and SMALL in one place.
Example
Read it as a sentence: add up V1:V5 using function 9 (SUM) and option 6 (ignore errors), so the error in V2 and the blank in V4 do not matter.
Syntax
AGGREGATE(function_num, options, ref1, [ref2], ...) or AGGREGATE(function_num, options, array, [k])
Arguments in [square brackets] are optional.
-
function_num(required) - A code 1-19 choosing the calculation (1 AVERAGE, 4 MAX, 5 MIN, 9 SUM, 14 LARGE, 15 SMALL and so on).
-
options(required) - A code saying what to skip: 0 nothing special, 5 hidden rows, 6 errors, 7 hidden rows and errors.
-
ref1(required) - The range or array to calculate over.
-
[ref2], ...(optional) - More ranges (codes 1-13), or for codes 14-19 the k value such as which largest item to return.
Returns: The chosen statistic, calculated while ignoring whatever the options code says to skip.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=AGGREGATE(9,6,Data!V1:V5) | 9 | 9 sum ignoring errors (6) |
=AGGREGATE(4,6,Data!V1:V5) | 5 | 4 max ignore errors |
=AGGREGATE(14,6,Data!V1:V5,2) | 3 | 14 LARGE k=2 |
=AGGREGATE(16,6,Data!A1:A5,0.5) | 3 | 16 PERCENTILE.INC |
=AGGREGATE(14,6,Data!A1:A5/(Data!A1:A5>2),1) | 5 | 14 with array arg |
=AGGREGATE(3,6,Data!B1:B8) | 7 | 3 counta mixed |
=AGGREGATE(9,0,Data!V1:V5) | #DIV/0! | 9 sum option 0 with error |
=AGGREGATE(15,6,Data!V1:V5) | #VALUE! | 15 SMALL k omitted |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Function codes 14-19 need the array form and a k argument.
- Ignoring hidden rows only works for rows, not hidden columns.
- Codes 1-13 need references, not arrays, when you pass multiple ranges.
Where it works
- Excel
- AGGREGATE arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- No direct equivalent. Sheets has no AGGREGATE; use SUBTOTAL, or filter errors out with FILTER and ISNUMBER.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .