AGGREGATE function
Quick answer
AGGREGATE runs one of 19 calculations, from SUM and AVERAGE to LARGE and PERCENTILE, and can skip error values, hidden rows and other subtotals while it does.
- Syntax
=AGGREGATE(function_num, options, array, [k])- Most common use
=AGGREGATE(1,6,D2:D5)returns $34.33 (The average price, skipping South's #DIV/0!)
Use it when a column has a stray #N/A or #DIV/0! that would break SUM or MAX, or with LARGE and SMALL to find the top values that meet a condition.
- Available in
- Excel 2010 and later
- Category
- Math functions
- Returns
- The result of the chosen calculation.
Syntax
=AGGREGATE(function_num, options, array, [k])
| Argument | Required? | What it is |
|---|---|---|
function_num | Required | Which calculation: 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 9 SUM, 12 MEDIAN, 14 LARGE, 15 SMALL, 16 PERCENTILE.INC and more. |
options | Required | What to skip: 6 skips errors, 5 hidden rows, 7 both; 0–3 also skip other SUBTOTAL and AGGREGATE results; 4 skips nothing. |
array | Required | The range to calculate on. Functions 1–13 take more ranges after it; 14–19 also accept a calculated array. |
k | Optional | For 14–19 only: which largest or smallest value, or which percentile or quartile. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =AGGREGATE(1,6,D2:D5) | $34.33 | The average price, skipping South's #DIV/0! |
| F3 | =AGGREGATE(4,6,D2:D5) | $35.00 | The highest price, again skipping the error |
| F4 | =AGGREGATE(14,6,B2:B5/(C2:C5>100),1) | $5,100 | The largest sales among stores that sold over 100 units |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Store | Sales | Units | Avg price | Result | |
| North | $4,200 | 120 | $35.00 | $34.33 | |
| South | $3,150 | 0 | #DIV/0! | $35.00 | |
| East | $5,100 | 150 | $34.00 | $5,100 | |
| West | $2,890 | 85 | $34.00 |
Practice AGGREGATE
Graded instantly, then re-checked on changed data.
Totals despite errors
D2:D7 shows #DIV/0! where no units were made. In G2, return the average cost per unit with AGGREGATE so the errors are skipped, and in G3 the highest cost per unit the same way.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | Functions 1–13 need a range, not a calculated array. For a condition, use 14 (LARGE) or 15 (SMALL) with k, which accept arrays. |
| Errors still break it | The options number decides what is skipped. Use 6 (or 7) to skip errors; 0 and 4 don't. |
| The wrong calculation | The numbers aren't alphabetical: 9 is SUM, 14 is LARGE, 15 SMALL. Excel lists them as you type. |
Tips
- =AGGREGATE(15,6,A2:A20/(B2:B20="West"),1) is the smallest value for West: the division turns other rows into errors, and option 6 skips them.
Questions
How do I sum a column that has #N/A errors?
=AGGREGATE(9,6,B2:B100) adds the numbers and skips the errors.
What does option 6 mean in AGGREGATE?
Ignore error values. 5 ignores hidden rows, 7 both, and 0–3 also ignore other SUBTOTAL and AGGREGATE results.
How is AGGREGATE different from SUBTOTAL?
AGGREGATE has 19 calculations instead of 11, can skip errors, and its LARGE, SMALL and percentile options accept calculated arrays.
Updated Checked by running: every formula on this page was run by our formula engine on .