Excel like a finance pro.
AGGREGATE
AggregationTotals that carry on past an #N/A instead of letting one error poison the report.
Difficulty
1What is it?
One #N/A from a failed lookup, and every SUM beneath it returns #N/A too — the whole total row of a report goes red because of a single missing code. AGGREGATE does SUM, AVERAGE, MAX and sixteen other calculations with an option to skip error values, hidden rows, or both. It is the honest fix for a report built on imperfect data: the totals still work, and the error is still visible in the row where it actually lives rather than smeared across every line below it.
2What it looks like
AGGREGATE(function_num, options, ref1, [ref2], …)- function_num
- Which calculation: 1 AVERAGE · 2 COUNT · 4 MAX · 5 MIN · 9 SUM · 14 LARGE · 15 SMALL (19 in all).
- options
- What to skip: 6 ignores error values · 5 ignores hidden rows · 7 ignores both. This argument is the point of the function.
- ref1, …
- The range to calculate over.
3When you use it
- Total a column that contains a few #N/A lookups without masking them.
- Sum only the rows a filter is showing, errors or not.
- Find the largest value in a range that has errors mixed in.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Toggle the broken lookup and compare SUM with AGGREGATE.
=AGGREGATE(9, 6, B2:B5)=SUM(B2:B5) → #N/A — the whole total row goes red| A | B | C | |
|---|---|---|---|
| 1 | Customer | Revenue | Result |
| 2 | Acme | 42,000 | =SUM(B2:B5) → #N/A — the whole total row goes red |
| 3 | Globex | 98,000 | =AGGREGATE(9, 6, B2:B5) → $203,000 |
| 4 | Initech | The error still shows in B4 where it lives. It just stops spreading to every total below it. | |
| 5 | Umbrella | 63,000 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Still returns #N/AThe option chosen doesn't skip errors — 5 skips hidden rows only.Fix: Use 6 (errors) or 7 (errors and hidden rows).
#VALUE!function_num is outside 1-19, or a LARGE/SMALL form was used without its k argument.Fix: Functions 14-19 need a fourth argument: AGGREGATE(14, 6, range, 1) is the largest.
The total is right but the errors vanished from viewNothing is wrong — AGGREGATE skips them in the TOTAL, not in the rows.Fix: That's the design. Fix the lookups; the total simply stops being hostage to them meanwhile.
Hidden rows still countedRows hidden by hand rather than by a filter, with an option that only ignores filtered rows in some versions.Fix: Option 5 or 7 ignores manually hidden rows too. SUBTOTAL's 9 does not; 109 does.
6Better functions & alternatives
- IFERROR inside SUM — =SUM(IFERROR(range, 0)) works but HIDES the errors by turning them into zero, which is worse.
- SUBTOTAL — Skips filtered rows but not errors. AGGREGATE is SUBTOTAL with an error option added.
Want AGGREGATE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.