Excel SUBTOTAL Function — Totals That Respect Your Filter (and the 9 vs 109 Trap)

TL;DR — SUBTOTAL is a total that knows about your filter. Plain SUM always adds every row; SUBTOTAL skips the rows a filter has hidden, so the figure at the bottom of a filtered list updates live. Syntax: =SUBTOTAL(function_num, range). The function_num picks the operation and the hiding behaviour: 9 = SUM ignoring filtered rows; 109 = SUM ignoring filtered and manually hidden rows. Its second superpower: SUBTOTAL ignores other SUBTOTALs in its range, so a grand total never double-counts your group subtotals. It does not skip error values — that's what AGGREGATE is for. Works in Excel 2003+.

=SUBTOTAL(9, Sales)        ' SUM of the filtered-visible rows
=SUBTOTAL(109, Sales)      ' SUM of rows visible after BOTH filtering and manual hiding

SUM has one job and does it bluntly: add everything, filter or no filter. That blindness is exactly the problem when you're working a filtered list and want the total of what you can see. SUBTOTAL is the answer — but it hides a famous trap in its very first argument, and a second feature almost nobody is taught that makes it the right tool for layered reports. Let's take both.

What you'll learn

• The mental model: a total that recalculates with your filter
• The function_num table — eleven operations, two families
• The 9 vs 109 trap: why hiding rows by hand doesn't change a 9 total
• Why SUBTOTAL ignores other SUBTOTALs — the no-double-count superpower
• The one thing it can't do: skip error values
• The judgment call: SUBTOTAL vs SUM vs SUMIFS

The mental model: a total that moves with your filter

Picture a sales list with an AutoFilter on. You filter to "West" and ten rows show. A =SUM(Sales) at the bottom still adds all the rows, visible or not — it has no idea a filter exists. =SUBTOTAL(9, Sales) adds only the rows the filter left visible, so the moment you change the filter to "East", the total recalculates to East's number. That's the entire concept: SUBTOTAL is a filter-aware aggregate. It's why Excel's Table Total Row and the Data → Subtotal feature both generate SUBTOTAL formulas rather than SUM.

The function_num table: eleven operations, two families

The first argument isn't just "9 means sum." It's a code that selects both the operation and how it treats hidden rows. There are eleven operations, each available in two families:

' 1–11  : the operation, ignoring FILTER-hidden rows (but counting manual hides)
' 101–111: the same operation, ALSO ignoring manually hidden rows
1/101 = AVERAGE   2/102 = COUNT    3/103 = COUNTA   4/104 = MAX
5/105 = MIN       6/106 = PRODUCT  7/107 = STDEV    8/108 = STDEVP
9/109 = SUM       10/110 = VAR     11/111 = VARP

So =SUBTOTAL(1, …) is a filter-aware AVERAGE; =SUBTOTAL(104, …) is a MAX that also ignores rows you hid by hand. You rarely need to memorise the table — but you do need to understand the two families, because that's where the trap lives.

The 9 vs 109 trap

Here's the bug that sends people to forums. You have =SUBTOTAL(9, Sales). You right-click a few rows and hide them manually (no filter), expecting the total to drop. It doesn't budge. Why?

Because the 1–11 family only ignores rows hidden by a filter. Rows you hid manually are still counted. To exclude manual hides too, you need the 101–111 family:

=SUBTOTAL(9,   Sales)      ' ignores filtered rows; COUNTS manually hidden rows
=SUBTOTAL(109, Sales)      ' ignores filtered rows AND manually hidden rows

The practical rule: if you only ever filter, 9 and 109 give the same answer. The moment you hide rows by hand, use the 100+ codes. When in doubt, default to 109 for sums and 103 for counts — they behave the way most people expect "total what I can see" to behave.

The superpower nobody teaches: SUBTOTAL ignores other SUBTOTALs

This is the feature that makes SUBTOTAL more than "a filtered SUM," and it's why it exists at all. A SUBTOTAL skips any other SUBTOTAL cells inside its range. Picture a report with a SUBTOTAL after each region's block, then one grand SUBTOTAL spanning the whole column:

B10  =SUBTOTAL(9, B2:B9)      ' West subtotal
B20  =SUBTOTAL(9, B11:B19)    ' East subtotal
B21  =SUBTOTAL(9, B2:B20)     ' GRAND total — does NOT double-count B10 and B20

A plain =SUM(B2:B20) would add the two group subtotals on top of the detail rows and double your grand total. SUBTOTAL quietly excludes the nested subtotals, so the grand total is correct without you carving the range around them. This is precisely how Data → Subtotal builds multi-level summaries that foot correctly — and once you know it, you'll reach for SUBTOTAL any time a column mixes detail rows with running subtotals.

The one thing it can't do: skip errors

SUBTOTAL is filter-aware, but it is not error-aware. A single #N/A or #DIV/0! anywhere in the range and the whole SUBTOTAL returns an error:

=SUBTOTAL(9, Sales)      ' one #N/A in Sales → the SUBTOTAL itself is #N/A

This is the exact gap AGGREGATE was created to fill: it does everything SUBTOTAL does and adds an option to ignore error values (and a wider set of functions). If your filtered column can contain errors — a VLOOKUP that misses, a divide-by-zero — that's your cue to step up to AGGREGATE.

The judgment call: SUBTOTAL vs SUM vs SUMIFS

They answer different questions, and mixing them up is the root of most "wrong total" confusion:

• SUM — total everything, ignoring filters. Right for a fixed grand total that shouldn't move when someone filters the sheet.
• SUBTOTAL — total what's visible after filtering (and optionally manual hiding). Right for the bottom of an interactive list, and the engine behind Table Total Rows.
• SUMIFS — total rows matching conditions you type into the formula, regardless of what's filtered on screen. Right for a report cell that must always mean "West Q1," whether or not the sheet is filtered to it.

The clean way to hold it: SUBTOTAL follows the filter; SUMIFS follows the criteria you wrote. If you want the number to react to the filter dropdowns, it's SUBTOTAL. If you want it pinned to a definition, it's SUMIFS.

How ExcelMaster helps

The SUBTOTAL mistakes are rarely about syntax — they're about choosing 9 versus 109, or not realising a grand total is double-counting nested subtotals. ExcelMaster reads what you're building — a total at the bottom of this filtered list that also ignores rows I hide, or a region report with subtotals and a grand total that foots — and writes the right function_num, the right range, and switches you to AGGREGATE the moment errors enter the picture. You describe the report; it picks the code.

Frequently asked questions

What's the difference between SUBTOTAL 9 and 109?

Both SUM the visible rows after a filter. The difference is manually hidden rows: 9 still counts rows you hid by right-click → Hide, while 109 ignores them too. If you only ever use the filter, they match. If you hide rows by hand, use 109.

Why isn't my SUBTOTAL changing when I hide rows?

You're probably using a 1–11 code (like 9), which only ignores filter-hidden rows, not manually hidden ones. Switch to the 101–111 family (e.g. 109) to make manual hiding affect the total.

Does SUBTOTAL ignore other SUBTOTAL cells?

Yes — that's a deliberate feature. A SUBTOTAL excludes any other SUBTOTAL results inside its range, so a grand total over a column that already contains group subtotals won't double-count them. Plain SUM would.

How do I sum only filtered/visible cells?

Use =SUBTOTAL(9, range) (or 109 to also skip manually hidden rows). For a Table, turn on the Total Row — it inserts SUBTOTAL for you and lets you pick the function from a dropdown.

How do I make SUBTOTAL skip error values?

It can't. Use AGGREGATE instead — =AGGREGATE(9, 6, range) sums the visible rows while ignoring errors. That extra options argument is the whole reason AGGREGATE exists.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-30.

Related guides: Excel AGGREGATE · Excel SUMIFS · Excel SUMPRODUCT · Excel FILTER · Excel COUNTIFS