SUBTOTAL with more functions and the option to ignore errors, hidden rows, and nested subtotals. It also exposes LARGE, SMALL, PERCENTILE, and QUARTILE, and its array form lets you do a conditional MAX or nth-largest in any Excel version.

Syntax

=AGGREGATE(function_num, options, array, [k])

Example

=AGGREGATE(14,6,A1:A100,1)

• Sum ignoring errors

  =AGGREGATE(9,6,C2:C6) → 130

  C2:C6 holds 10, #N/A, 30, 40, 50. Option 6 ignores the error and sums the rest.

• Largest value ignoring errors

  =AGGREGATE(14,6,C2:C6,1) → 50

  Function 14 is LARGE; the trailing 1 asks for the largest.

• Conditional largest

  =AGGREGATE(14,6,C2:C6/(B2:B6="West"),1) → 40

  Dividing by FALSE creates #DIV/0! for non-West rows, which option 6 ignores. A conditional MAX without MAXIFS.

• Option 6 ignores error values, which makes LARGE over a column with #N/A possible.
• Options: 0 ignore nested SUBTOTAL/AGGREGATE, 1 also hidden rows, 2 also errors, 3 all of those, 4 nothing, 5 hidden rows, 6 errors, 7 hidden rows and errors.
• Functions 14 to 19 (LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC) take a k argument at the end.
• The array form (functions 14 to 19) accepts expressions like range/(condition) without Ctrl+Shift+Enter.

• Functions 1 to 13 need a range, not an array expression; the array trick only works with 14 to 19.
• Ignoring errors can hide real data problems. Keep a separate error count.