TL;DR —
AGGREGATEisSUBTOTALwith two upgrades: 19 functions (it addsLARGE,SMALL,MEDIAN,PERCENTILE,QUARTILEand more) and anoptionsargument that lets it ignore error values, hidden rows, and nested totals. Syntax:=AGGREGATE(function_num, options, range, [k]). Its most underused power: sum or average a column that contains#N/Awithout anyIFERRORcleanup —=AGGREGATE(9, 6, range). The catch: it has two lead arguments whereSUBTOTALhas one, and the14–19functions (LARGE/SMALL/PERCENTILE/QUARTILE) need an extrak. Works in Excel 2010+.
=AGGREGATE(9, 6, Sales) ' SUM ignoring error values
=AGGREGATE(14, 6, Sales, 1) ' LARGEST value, ignoring errors (the k=1 at the end)
If you've read the SUBTOTAL guide, you already
know 80% of AGGREGATE — it's the same filter-aware idea. The reason to learn the
other 20% is one scenario that comes up constantly in real data: a column with
errors in it that you still need to total. SUBTOTAL chokes on a single #N/A;
AGGREGATE shrugs and computes. That alone earns it a place in your toolkit.
What you'll learn
- The mental model:
SUBTOTALplus errors-and-more, controlled byoptions - The two-argument structure — and why people forget the
options - The killer trick: total a column full of
#N/Awith no cleanup - The
optionsgrid: what each number 0–7 ignores - The
14–19array functions and the extrakargument - The judgment call:
AGGREGATEvsSUBTOTALvsIFERRORwrappers
The mental model: SUBTOTAL with a control dial
SUBTOTAL gives you a filter-aware total but only one decision — which operation.
AGGREGATE keeps the filter-awareness and adds a second dial: an options
argument that decides what else to skip. Think of it as
SUBTOTAL(operation, range) grown a new knob in the middle:
=SUBTOTAL(9, Sales) ' SUM, skipping filtered rows
=AGGREGATE(9, 6, Sales) ' SUM, skipping filtered rows AND error values (option 6)
The other half of the upgrade is reach: SUBTOTAL offers 11 operations,
AGGREGATE offers 19 — the same statistical set plus MEDIAN, MODE.SNGL,
LARGE, SMALL, PERCENTILE.INC/EXC, and QUARTILE.INC/EXC. So you can take the
5th-largest value of a filtered column that contains errors in one function —
something no other single formula does cleanly.
The structure that trips people up: two lead arguments
The single most common AGGREGATE mistake is treating it like SUBTOTAL and
forgetting the second argument. SUBTOTAL(function_num, range) has one code;
AGGREGATE(function_num, options, range) has two:
=AGGREGATE(9, Sales) ' WRONG — Excel reads "Sales" as the options, errors out
=AGGREGATE(9, 6, Sales) ' RIGHT — 9 = SUM, 6 = ignore errors, then the range
Burn in the order: what to compute, what to ignore, then the data. Once that's muscle memory, the rest is just looking up two small tables.
The killer trick: total a column that contains errors
This is the reason to reach for AGGREGATE over SUBTOTAL, and it's badly
underused. Real columns are messy — a VLOOKUP that misses returns #N/A, a ratio
divides by zero. SUM and SUBTOTAL both fail on the first error. The old fix was
to wrap every cell in IFERROR in a helper column, or write a Ctrl+Shift+Enter
array formula. AGGREGATE does it inline:
=AGGREGATE(9, 6, Margin) ' sum the margins, just skip the #DIV/0! rows
=AGGREGATE(4, 6, Lookups) ' MAX of a column full of #N/A — no IFERROR needed
=AGGREGATE(1, 6, Margin) ' AVERAGE, errors excluded from both sum and count
Option 6 means "ignore error values." No cleanup column, no array entry — the
errors are simply left out of the calculation. For a dashboard pulling from raw
data that will contain errors, this is the cleanest tool Excel has.
The options grid
The options argument (0–7) is a combination of three switches: ignore nested
SUBTOTAL/AGGREGATE, ignore hidden rows, ignore errors. The useful ones:
' 0 (or omitted) : ignore nested SUBTOTAL/AGGREGATE only
' 1 : also ignore hidden rows
' 2 : also ignore error values
' 3 : ignore hidden rows AND errors
' 4 : ignore nothing
' 5 : ignore hidden rows
' 6 : ignore error values ← the one you'll use most
' 7 : ignore hidden rows AND errors
In practice you'll reach for 6 (errors) and 3 or 7 (hidden rows + errors)
almost every time. Note the difference from SUBTOTAL: here, ignoring hidden rows
is an option flag, not baked into the function number.
The 14–19 functions and the extra k
Six of the functions aren't simple aggregates — they ask "which one?" and need an
extra k argument at the end:
' 14 = LARGE, 15 = SMALL, 16 = PERCENTILE.INC, 17 = QUARTILE.INC,
' 18 = PERCENTILE.EXC, 19 = QUARTILE.EXC
=AGGREGATE(14, 6, Sales, 1) ' 1st LARGEST (max), ignoring errors
=AGGREGATE(15, 6, Sales, 2) ' 2nd SMALLEST, ignoring errors
=AGGREGATE(16, 6, Sales, 0.9) ' 90th PERCENTILE, ignoring errors
Forget the k on one of these and you get #VALUE!. The payoff is real, though: a
robust MIN that ignores errors is =AGGREGATE(15, 6, range, 1) — something plain
MIN can't do when the column has an #N/A in it. (For these statistics on their
own, the dedicated LARGE, SMALL, and PERCENTILE functions are simpler — reach
for AGGREGATE when you also need the filter-awareness or error-skipping.)
The judgment call: AGGREGATE vs SUBTOTAL vs IFERROR
- Everyday filtered total, clean data →
SUBTOTAL. It's shorter, and it's what Table Total Rows insert natively. No reason to type the extraoptionsargument when there are no errors to skip. - Filtered total where the data contains errors, or you need a function
SUBTOTALlacks (LARGE/SMALL/MEDIAN/PERCENTILE) →AGGREGATE. This is its sweet spot. - You only want to neutralise errors in a normal formula →
IFERRORis fine for wrapping a single result. But if you're tempted to wrap a whole range inIFERRORinside a CSE array just to sum past errors, that's a detour —AGGREGATE(9, 6, range)is one function and no array entry.
The strong opinion: in 2026, a Ctrl+Shift+Enter {=SUM(IF(ISERROR(…)))} written
only to skip errors is legacy. AGGREGATE replaced it years ago. Keep SUBTOTAL
for clean filtered lists; escalate to AGGREGATE the instant errors or
percentiles enter the range.
How ExcelMaster helps
AGGREGATE's power is gated behind two lookup tables — the 19 function codes and
the 8 options — which is exactly the kind of friction that stops people using it.
ExcelMaster takes the intent — sum this column but skip the lookup errors,
or the 5th-largest value among the visible rows, ignoring blanks and errors — and
writes AGGREGATE with the correct function_num, the right options, and the
k argument where it's needed. You never touch the grids.
Frequently asked questions
How do I sum a column that has errors in it?
Use =AGGREGATE(9, 6, range). Function 9 is SUM and option 6 means "ignore
error values," so any #N/A or #DIV/0! rows are simply left out — no IFERROR
helper column required.
What's the difference between AGGREGATE and SUBTOTAL?
AGGREGATE is a superset. It offers 19 functions versus SUBTOTAL's 11 (adding
LARGE, SMALL, MEDIAN, PERCENTILE, QUARTILE), and adds an options argument that can
ignore error values and nested totals. SUBTOTAL can't skip errors at all. Use
SUBTOTAL for clean filtered lists, AGGREGATE when errors or those extra
functions are involved.
Why does my AGGREGATE return #VALUE!?
Two common causes: you forgot the options argument (it comes second, before the
range), or you used a 14–19 function (LARGE/SMALL/PERCENTILE/QUARTILE)
without the trailing k argument. =AGGREGATE(14, 6, range, 1) needs that final 1.
What do the AGGREGATE options numbers mean?
They control what's ignored: 0 ignores nested subtotals, 1/5 ignore hidden
rows, 2/6 ignore errors, 3/7 ignore both, 4 ignores nothing. The most
useful is 6 (ignore errors) and 7 (ignore hidden rows and errors).
Does AGGREGATE work in Excel 2016 or 2019?
Yes. AGGREGATE shipped in Excel 2010 and works in every version since, including
2016, 2019, 2021, and 365. Only Excel 2007 and earlier lack it — there, fall back to
SUBTOTAL plus an IFERROR cleanup.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-06-30.
Related guides: Excel SUBTOTAL · Excel SUMPRODUCT · Excel SUMIFS · Excel IFERROR · Excel FILTER