Syntax
AGGREGATE(function_num, options, ref1, ...)
| Parameter | Description |
|---|---|
| function_num | Parameter of the AGGREGATE function. |
| options | Parameter of the AGGREGATE function. |
| ref1 | Parameter of the AGGREGATE function. |
| ... | Parameter of the AGGREGATE function. |
Examples
Sum ignoring errors
Formula
=AGGREGATE(9, 6, A1:A100)
Returns the SUM of A1:A100, skipping any #N/A, #VALUE!, or other error cells. Option 6 = ignore error values. Regular SUM would return an error.
2nd largest, skip errors
Formula
=AGGREGATE(14, 6, B2:B100, 2)
Returns the 2nd largest value in B2:B100, ignoring errors. Function 14 = LARGE, option 6 = ignore errors, k=2.
Average visible, no errors
Formula
=AGGREGATE(1, 7, C2:C50)
Returns AVERAGE of visible cells, ignoring errors. Option 7 = ignore hidden rows AND error values.
Common Errors
#VALUE!
Invalid function_num (must be 1-19) or invalid option (must be 0-7). Also occurs if all values are errors and you're ignoring errors — nothing left to aggregate.
Tips
Options reference
0=ignore nested. 1=ignore hidden. 2=ignore errors. 3=ignore hidden+nested. 4=ignore nothing. 5=ignore hidden. 6=ignore errors+nested. 7=ignore all.
Extra functions beyond SUBTOTAL
AGGREGATE adds: 12=MEDIAN, 13=MODE, 14=LARGE, 15=SMALL, 16=PERCENTILE.INC, 17=QUARTILE.INC, 18=PERCENTILE.EXC, 19=QUARTILE.EXC.
Error-proof formulas
AGGREGATE(9,6,range) is the error-proof version of SUM. Use it whenever your data might contain #N/A or other errors from VLOOKUP, INDEX/MATCH, etc.
Try AGGREGATE in Viztab
Import your data and use AGGREGATE with 370+ other formulas. No signup required.
Open Viztab