Excel AGGREGATE function: ignore errors and hidden rows

Halfway through the month a finance sheet grew a red row: one cell somewhere in the column returned #DIV/0!, and every =SUM that touched it collapsed into the same error. Wrap it in IFERROR? That silences the noise, but the running totals in row 200 still stop making sense as soon as a filter hides a chunk of rows. AGGREGATE is the function Excel added for exactly this pair of problems — a column that mixes clean numbers, error cells, and rows the reader has filtered away — and it handles both in one formula, without an IFERROR wrapper and without switching to SUBTOTAL.

Why SUM lies about your data

Two situations quietly break the sum at the bottom of a column. The first is a single error cell — a division by zero, a bad lookup, a stray #REF! — which poisons every ordinary formula that touches the range. The second is filters. A pivot-adjacent totals row shows the total for everything in the sheet, not everything visible on screen, which is almost never what the reader wants.

The usual patches each have a cost. =SUM(IFERROR(A2:A200,0)) hides the errors but also hides the fact that a lookup broke — the underlying data problem is now invisible. SUBTOTAL respects filters but ignores errors only by accident, and it silently swallows other SUBTOTAL rows nested inside its range, which is a separate feature you have to remember.

The point of AGGREGATE is that you say out loud what you want ignored — errors, hidden rows, both, or neither — and get one clean result. The messy cells stay visible where they belong; the total just refuses to be dragged down by them.

What AGGREGATE actually does

The function takes a math operation as a number, an “ignore what” option as a number, and the range. Two forms exist because a few of the underlying functions need an extra k argument (LARGE, SMALL, PERCENTILE). Both forms live inside one AGGREGATE call — the k does not sit outside it.

=AGGREGATE(function_num, options, ref1, [ref2], …)
=AGGREGATE(function_num, options, array, [k])

function_num is 1 through 19 and picks the operation: 1 is AVERAGE, 9 is SUM, 4 is MAX, 14 is LARGE, and so on down the standard statistical set. options is 0 through 7 and picks what to skip. Everything else is the range, exactly like SUM. Per the Microsoft AGGREGATE reference, the function is available from Excel 2010 onward and works in both Windows and Mac builds.

The two arguments people forget

Beginners write =AGGREGATE(9, A2:A200) and get a #VALUE! back. Excel is not being difficult — the second argument is mandatory. Even if you want to ignore nothing, you write 4 in the options slot. Think of it as “always name the operation, always name the exclusion, then hand it the data.”

The options themselves are a lookup by intent. Decide what you want to skip in the range, read the row, use that number. Every option also skips nested AGGREGATE and SUBTOTAL results except for options 4 through 7 — the “no nested skip” half of the table.

The distinction between the top four rows and the bottom four looks fussy, and most beginner formulas will only ever use 3, 6, or 7. Reach for the 0–3 half when you are building a summary block that sits above other AGGREGATE or SUBTOTAL rows — Excel then treats those subtotal rows as invisible so your grand total does not double count.

Ignoring errors without an IFERROR wrapper

The most common reason people meet AGGREGATE is a single #DIV/0! or #N/A in the middle of a column. The old fix — wrapping every cell in IFERROR — spreads a fix across two hundred rows to solve a problem in one. AGGREGATE with option 6 collapses that into one formula.

Say column B holds a per-row conversion rate — clicks over impressions — and a couple of impression cells came in at zero. The formulas in column B return #DIV/0!, and the average at the bottom is broken. This is what the range looks like:

The average you actually want is 0.034 — Alpha, Charlie, and Delta divided by three, with Bravo left out. =AVERAGE(B2:B5) returns #DIV/0!. =AGGREGATE(1, 6, B2:B5) returns 0.034. The two arguments in front are the operation (1 for AVERAGE) and the exclusion (6 for “errors only”).

=AVERAGE(IFERROR(B2:B5,""))

=AGGREGATE(1, 6, B2:B5)

That last point matters more than the keystrokes saved. A total that quietly rejects broken cells still leaves them broken in the sheet, so the next reader can find and repair them. See the site’s Excel error troubleshooting notes for what each error type actually signals before you decide to skip it.

Playing nicely with filters

The other everyday use is running totals on filtered data. SUBTOTAL has done this job since the 1990s, and for a plain sum on a filtered list it is still fine. AGGREGATE earns its place when the range also contains error cells, because SUBTOTAL propagates errors instead of skipping them.

Options 5 and 7 are the two you actually type. Option 5 ignores hidden rows only — errors still surface, which is often what you want mid-analysis. Option 7 ignores hidden rows and errors together, for the report row a stakeholder will see.

=AGGREGATE(9, 5, D2:D5000)     // sum of visible rows, errors still surface
=AGGREGATE(9, 7, D2:D5000)     // sum of visible rows, errors quietly skipped
=SUBTOTAL(9, D2:D5000)         // sum of visible rows, but one #N/A breaks it

The number 9 in front of the range is “SUM” — the same code SUBTOTAL uses. If you have muscle memory for SUBTOTAL codes 1 through 11, they map to the same operations at the same positions in AGGREGATE’s 1 through 11.

Autofilter, not manual row hiding

“Hidden rows” here means rows the user has hidden with an Autofilter or by right-clicking and choosing Hide. It does not distinguish between the two ways rows disappear. If you filter a table and also hide a row manually, both are excluded — there is no option to skip only one class.

LARGE, SMALL, and the k argument

Six of the nineteen functions need a “which one?” number: LARGE (14), SMALL (15), PERCENTILE.INC (16), QUARTILE.INC (17), PERCENTILE.EXC (18), QUARTILE.EXC (19). That number is k. It goes inside the AGGREGATE call as the last argument, not after it — a common mistake is writing =AGGREGATE(14, 6, A2:A100), 2, which is a syntax error, instead of =AGGREGATE(14, 6, A2:A100, 2).

A concrete use: the second-highest sales figure in a column that also holds a #N/A from a broken lookup.

=AGGREGATE(14, 6, Sales[Amount], 2)

14 is LARGE, 6 ignores errors, Sales[Amount] is a structured reference to the table column, and 2 asks for the second largest. LARGE alone would have returned #N/A. Percentiles work the same way — =AGGREGATE(16, 6, Latency, 0.95) reads “give me the 95th percentile of the Latency range, ignoring any error cells.”

Common gotchas

Three limits catch people out. Fix them once and the function behaves.

• ✓ Horizontal ranges do not respect hidden columns. AGGREGATE was designed for vertical ranges. Hide a column and its values still count. The Microsoft reference above notes this explicitly; there is no workaround inside the function.
• ✓ Deleted rows are not hidden rows. A row you deleted is gone from the range’s perspective, and no option is needed. Options 1, 3, 5, and 7 only affect rows that still exist but are not showing.
• ✓ Text values are always ignored. Numeric functions like SUM and AVERAGE skip text automatically, regardless of option. Do not add an option 6 to “handle text” — it is not what option 6 is for.

One more, worth its own paragraph: AGGREGATE in a dynamic-array context spills like any other function, and if a stray error appears inside the spilled range it will not automatically get skipped by a downstream total. Skipping happens inside a single AGGREGATE call, not across the sheet. If your dynamic array is throwing spill errors of a different kind, the site’s spill-error guide walks through the usual causes.

When to reach for something else

Two adjacent cases are not AGGREGATE’s job. To sum only rows that match a condition, use SUMIFS. To strip empty entries from a text range so numeric functions run cleanly, use TEXTJOIN with the ignore-empty flag — see the site’s TEXTJOIN notes for the shape of that argument. AGGREGATE only decides what to skip inside an existing operation; it does not filter by content.

Turning it into muscle memory

Faced with a broken total, the shortest path from problem to formula is a four-step check. Run through it once and the option number falls out naturally:

1. Is the range one vertical column? If not, AGGREGATE is the wrong tool — use SUMIFS or a helper column.
2. Do errors exist in the range you want left out? If yes, option 6 or 7.
3. Is the sheet filtered or are rows hidden by hand? If yes, option 5 or 7.
4. Are other AGGREGATE or SUBTOTAL rows sitting inside the range? If yes, drop into the 0–3 half so those subtotal rows are ignored too.

Then pick the operation number — 1 AVERAGE, 9 SUM, 4 MAX, 14 LARGE — and write the formula. Once the shape is muscle memory (operation, exclusion, range), the messy sheets that used to require an IFERROR patch and a helper column collapse into a single line. Start with option 6 the next time an errant #DIV/0! breaks a monthly sum; add a 7 when a filter enters the picture; keep the broken cells where they are so someone can still fix them.