Excel functions / Math
AGGREGATE function
Like SUBTOTAL with more calculations, and it can ignore errors in the range.
Syntax
=AGGREGATE(function_num, options, ref, [k1, [k2], …])
Arguments
function_num: 9 SUM, 1 AVERAGE, 4 MAX, 5 MIN, 12 MEDIAN, 14 LARGE, 15 SMALL and others, 1 to 19.options: What to ignore: 6 errors, 5 hidden rows, 7 both, 4 nothing. 0 to 3 also ignore other subtotals.ref: The range to work on.k(optional): For LARGE, SMALL, PERCENTILE and QUARTILE (14 to 19): which one. Otherwise, more ranges.
When to use it
AGGREGATE is SUBTOTAL with more control. It can add, average, count and rank like SUBTOTAL, and a second argument says what to leave out: hidden rows, error values, or both.
Its most common use is totalling a column that has errors in it. One #N/A makes SUM return #N/A; =AGGREGATE(9, 6, …) adds the rest.
Examples
Add up a column that has an error in it
A B C D 1 Item Price 2 Pen 1.2 10.1 3 Folder #N/A 4 Stapler 8.9 In D2:
=AGGREGATE(9, 6, B2:B4)→ 10.19means sum, and6means ignore errors.=SUM(B2:B4)would return #N/A.The average, ignoring errors
A B C D 1 Item Price 2 Pen 1.2 5.05 3 Folder #N/A 4 Stapler 8.9 In D2:
=AGGREGATE(1, 6, B2:B4)→ 5.05The largest value, ignoring errors
A B C D 1 Item Price 2 Pen 1.2 8.9 3 Folder #N/A 4 Stapler 8.9 In D2:
=AGGREGATE(14, 6, B2:B4, 1)→ 8.914is LARGE, which needs a fourth argument: 1 for the largest, 2 for the second largest.
Common mistakes
- Leaving out the options argument. The second argument is required.
6ignores errors,5ignores hidden rows,7ignores both, and4ignores nothing. - No fourth argument for 14 to 19. LARGE, SMALL, the percentiles and the quartiles (14 to 19) need to know which one:
=AGGREGATE(14, 6, B2:B10, 1). Without it the result is #VALUE!. - Hiding a problem. Ignoring errors gives a total, but the errors are still in the data. Check why they are there before trusting the figure.
Common questions
How do I sum a column and ignore errors in Excel?
Use =AGGREGATE(9, 6, B2:B100). The 9 means sum and the 6 means ignore error values, so cells showing #N/A or #DIV/0! are left out of the total.
AGGREGATE in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: AGGREGATE
- Danish: SAMLING
- Dutch: AGGREGAAT
- Finnish: KOOSTE
- French: AGREGAT
- German: AGGREGAT
- Hungarian: ÖSSZESÍT
- Italian: AGGREGA
- Norwegian: MENGDE
- Polish: AGREGUJ
- Portuguese (Brazil): AGREGAR
- Portuguese (Portugal): AGREGAR
- Russian: АГРЕГАТ
- Swedish: MÄNGD
- Turkish: TOPLAMA
Try AGGREGATE in the playground
Related functions
- SUBTOTAL: Totals a range, leaving out rows that a filter hides and other subtotals inside it.
- IFERROR: Returns a fallback value if a formula results in an error.
- SUM: Adds up numbers, cells and ranges.
- LARGE: The k-th largest value (k = 1 is the maximum).
- SMALL: The k-th smallest value (k = 1 is the minimum).