Math function
Excel AGGREGATE function
AGGREGATE runs one of nineteen calculations, from SUM and AVERAGE to LARGE, SMALL and PERCENTILE, and can ignore error values, nested subtotals and hidden rows. Its best-known use is totalling a column that contains #DIV/0! or #N/A without cleaning it first.
=AGGREGATE(function_num, options, ref1, [ref2_or_k], ...)Arguments
function_num- 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- What to ignore: 0 nested SUBTOTAL and AGGREGATE, 1 those plus hidden rows, 2 those plus errors, 3 all three, 4 nothing, 5 hidden rows, 6 errors, 7 hidden rows and errors.
ref1- The range. For 14 to 19 it can be a calculated array, such as C2:C7/(B2:B7>10).
ref2_or_k- For 1 to 13, more ranges. For 14 to 19, k: which largest or smallest, or the percentile or quartile.
AGGREGATE examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Units | Revenue ($) | Price per unit |
| 2 | Desk | 12 | 2400 | 200 |
| 3 | Chair | 0 | 0 | #DIV/0! |
| 4 | Lamp | 30 | 1350 | 45 |
| 5 | Shelf | 8 | 1520 | 190 |
| 6 | Mat | 0 | 0 | #DIV/0! |
| 7 | Stool | 15 | 975 | 65 |
Result: 125
Option 6 ignores the two #DIV/0! cells and averages the four real prices: 125. AVERAGE(D2:D7) would return #DIV/0!.
Result: 200
The highest price per unit, ignoring errors: 200, the desk.
Result: 2400
The largest revenue among products with more than 10 units: 2,400. Rows that fail the test divide by 0, and option 6 drops those errors.
Result: 65
Function 15 is SMALL, and k = 2 asks for the second-lowest price: 65.
How to use AGGREGATE
- Click the cell for the result.
- Type =AGGREGATE( and the function number, such as 9 for SUM.
- Type the option, 6 to ignore errors.
- Select the range; for 14 to 19 add a comma and k.
- Press Enter.
Common AGGREGATE mistakes
A calculated array with SUM or AVERAGE
Only functions 14 to 19 accept an array such as C2:C7/(B2:B7>10). With 1 to 13 the third argument must be a cell range, or AGGREGATE returns #VALUE!.
Leaving out k for 14 to 19
LARGE, SMALL, PERCENTILE and QUARTILE need the fourth argument. AGGREGATE(14,6,D2:D7) returns #VALUE!.
Hiding errors you should fix
Option 6 makes errors invisible in the total. Check with COUNT or ISERROR that you are not skipping rows by accident.
AGGREGATE FAQ
How do I sum a column with errors in Excel?
Use =AGGREGATE(9,6,range). Function 9 is SUM and option 6 ignores error values.
What is the difference between AGGREGATE and SUBTOTAL?
SUBTOTAL has 11 functions and cannot skip errors. AGGREGATE has 19, can ignore errors, and accepts calculated arrays for LARGE, SMALL, PERCENTILE and QUARTILE. Both skip nested subtotals.
How do I get the largest value with a condition without MAXIFS?
Use =AGGREGATE(14,6,values/(criteria_range=criteria),1). It works in Excel 2010 and later without Ctrl+Shift+Enter.
Guides that use AGGREGATE
- How to sum a column in Excel
- How to sum only visible cells in Excel
- #N/A error in Excel: causes and fixes
- #DIV/0! error in Excel
Related functions
SUBTOTALTotal that skips other subtotalsIFERRORFallback when a formula errorsLARGEk-th largestMAXIFSLargest value that matches
Updated