AGGREGATE Function
Runs one of 19 calculations while skipping errors, hidden rows or nested subtotals.
Category
Math & Trig
Available in
Excel 2010+, Microsoft 365
Returns
A number
Overview
AGGREGATE is SUBTOTAL's more powerful sibling. It offers 19 calculations — including MEDIAN, LARGE, SMALL and PERCENTILE — and an options number that decides what to skip: errors, hidden rows, other subtotals, or a mix.
Its best-known trick: summing or finding the largest value in a column that contains #N/A or #DIV/0! errors, without cleaning the data first.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| function_num | Yes | 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV.S, 8 STDEV.P, 9 SUM, 10 VAR.S, 11 VAR.P, 12 MEDIAN, 13 MODE.SNGL, 14 LARGE, 15 SMALL, 16 PERCENTILE.INC, 17 QUARTILE.INC, 18 PERCENTILE.EXC, 19 QUARTILE.EXC. |
| options | Yes | What to skip: 0 nested subtotals, 1 + hidden rows, 2 + errors, 3 all three, 4 nothing, 5 hidden rows, 6 errors, 7 hidden rows and errors. |
| ref1 / array | Yes | The range (functions 1–13) or array (14–19) to calculate. |
| k | For 14–19 | Which largest/smallest value, percentile or quartile. |
Examples
Example 1 — Totalling past the errors
A lookup column has #N/A for missing products. SUM fails; AGGREGATE with option 6 skips the errors.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Total |
| 2 | Widget | 4.5 | 20.5 |
| 3 | Gizmo | #N/A | |
| 4 | Gadget | 12 | |
| 5 | Cable | 4 |
Example 2 — Largest sale in one region
Dividing by a TRUE/FALSE test turns non-West rows into #DIV/0! errors, which option 6 then ignores.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | Top West sale | |
| 2 | West | 620 | 620 | |
| 3 | East | 900 | ||
| 4 | West | 480 | ||
| 5 | West | 550 |
Common mistakes
- Array form needs 14–19. Only LARGE, SMALL, PERCENTILE and QUARTILE (14–19) accept a calculated array like B2:B5/(A2:A5="West"). With 1–13 it must be a real range.
- Hidden columns aren't skipped. Like SUBTOTAL, the hidden options only apply to rows.
Notes
- In the Practice Sandbox, AGGREGATE skips errors and nested subtotals; rows you hide by hand are still counted.
- Related functions: SUBTOTAL, LARGE, SMALL, IFERROR.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.