# AGGREGATE function in Excel

> 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.

Source: https://spreadsheetpractice.com/functions/aggregate · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** 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!)
- **Available in:** Excel 2010 and later
- **Category:** [Math functions](https://spreadsheetpractice.com/functions/category/math)
- **Returns:** The result of the chosen calculation.

## What it does

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. 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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Store | Sales | Units | Avg price |  | Result |
| 2 | North | $4,200 | 120 | $35.00 |  | $34.33 |
| 3 | South | $3,150 | 0 | #DIV/0! |  | $35.00 |
| 4 | East | $5,100 | 150 | $34.00 |  | $5,100 |
| 5 | West | $2,890 | 85 | $34.00 |  |  |

## 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.

## Related functions

- [SUBTOTAL](https://spreadsheetpractice.com/functions/subtotal): SUBTOTAL calculates a sum, average, count or other total for a range while skipping any other SUBTOTAL formulas inside it, so section subtotals and a grand total never double count.
- [IFERROR](https://spreadsheetpractice.com/functions/iferror): IFERROR returns a value you choose when a formula results in an error, and the formula's normal result otherwise.
- [LARGE](https://spreadsheetpractice.com/functions/large): LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.

## 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.

## Practice

A graded AGGREGATE exercise ("Totals despite errors") is on [the AGGREGATE page](https://spreadsheetpractice.com/functions/aggregate). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.