The Excel AGGREGATE function applies one of 19 operations to a range while optionally ignoring errors, hidden rows, and nested subtotals — a more powerful successor to SUBTOTAL.
Syntax
| Argument | Description | |
|---|---|---|
function_num | Required | 1-19, choosing the operation: 9 = SUM, 1 = AVERAGE, 4 = MAX, 14 = LARGE, 15 = SMALL, and more. |
options | Required | 0-7, choosing what to ignore: e.g. 5 = hidden rows, 6 = error values, 7 = both hidden rows and errors. |
ref1 / array | Required | The range to aggregate. Reference functions (1-13) take ranges; array functions (14-19, like LARGE) take an array plus a k. |
[ref2...] / [k] | Optional | More ranges, or the k argument for array functions such as LARGE/SMALL/PERCENTILE. |
How to use it
AGGREGATE takes two control numbers: function_num (what to calculate) and options (what to ignore). It can do everything SUBTOTAL does, plus skip error values and run array functions like LARGE and SMALL.
Function codes 1–13 work on ranges; codes 14–19 (LARGE, SMALL, PERCENTILE, QUARTILE, …) take an extra k argument. The standout feature is option 6, which lets a total survive a range that contains error cells.
Sum past errors: if a column has stray #DIV/0! cells, =AGGREGATE(9,6,range) totals the good numbers and ignores the errors — something plain SUM can't do.
Try it: interactive demo
Pick a AGGREGATE example to see the formula and its result.
Practice workbook
Frequently asked questions
How is AGGREGATE different from SUBTOTAL?
What do the options numbers mean?
How do I get the kth largest value ignoring errors?
=AGGREGATE(14,6,range,1) returns the largest value while ignoring any error cells; change 1 to 2 for the second largest.Which Excel versions have AGGREGATE?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class