AGGREGATE
Returns an aggregate calculation (SUM, AVERAGE, etc.) with options to ignore errors and hidden rows.
AGGREGATE(function_num, options, ref1, [ref2], ...)Arguments
function_numFunction (1=AVERAGE, 4=MAX, 5=MIN, 9=SUM, etc.)optionsWhat to ignore (6=errors, 7=errors+hidden)ref1First rangeref2Additional arguments(optional)
=AGGREGATE(9,6,A1:A100)Sum that skips error cells
=AGGREGATE(4,6,B:B)Max without #N/A issues
=AGGREGATE(14,6,A:A,2)LARGE ignoring errors
- •More flexible than SUBTOTAL
- •Can ignore errors, hidden rows, or both
- •Functions 14-19 need k argument
- •Mixing up the function_num and options codes - they're both just numbers, and using the wrong one silently produces a different calculation instead of raising an error
- •Forgetting that functions 14 through 19 (like LARGE, SMALL, PERCENTILE) require an extra k or similar argument after ref1, which a simple SUM or AVERAGE call doesn't need
- •Using AGGREGATE for a simple case where SUM or AVERAGE alone would work fine, adding unnecessary complexity when there are no errors or hidden rows to worry about
SUBTOTALOffers similar error/hidden-row handling but with fewer function options and a different numbering scheme than AGGREGATE.SUMIFHandles conditional summing, a different kind of filtering than AGGREGATE's error/visibility-based options.IFERRORCan achieve similar error-skipping behavior in a single SUM or AVERAGE formula, though less flexibly than AGGREGATE's dedicated options.What's the difference between AGGREGATE and SUBTOTAL?
AGGREGATE supports more functions (19 versus SUBTOTAL's 11) and offers more granular control over what to ignore - errors, hidden rows, or both - through its options argument.
Why do I need an extra argument for some AGGREGATE function numbers?
Function numbers 14 to 19 (like LARGE, SMALL, and the PERCENTILE family) need an additional argument, such as which rank or percentile to return, beyond the basic range.
What do the options codes in AGGREGATE mean?
They control what AGGREGATE ignores - for example, 6 ignores error values, and 7 ignores both errors and hidden rows. Check Excel's documentation for the full list of codes.
Need to translate a formula using AGGREGATE?
Use our translator to convert your complete formula