Your Excel Total Includes the Rows You Filtered Out

SUM does not know the filter exists. You filter a list to one region, read the total at the bottom, and quote a figure that includes every row you just hid.

One function fixes it:

=SUBTOTAL(109, D2:D21)

The 109 matters more than most people realise, and the rest of this explains why.

Six rows are on screen. SUM is still adding all twenty, and nothing about the total says so.

Why nobody catches it

Because Excel tells you the right answer somewhere else.

Select the filtered column and look at the status bar along the bottom. The Sum it shows there counts visible cells only. So the status bar says one number, the cell holding =SUM() says another, and the two disagree by however much you filtered out.

Almost nobody has both in view at once. You read the cell, because the cell is the one that goes in the report.

⚠️ Filtering hides rows. It does not remove them, and no ordinary formula treats a hidden row differently from a visible one. SUM, AVERAGE, COUNT, MAX and every SUMIF you have ever written all ignore the filter completely.

SUBTOTAL, and the two numbering systems

SUBTOTAL takes a code telling it which calculation to perform:

=SUBTOTAL(function_num, range)

There are two codes for every calculation — a low one and a high one — and this is the part that catches people:

Calculation	1–11	101–111
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
SUM	9	109

Both ranges ignore rows hidden by a filter. The difference is rows you hid by hand — right-click, Hide.

• 9 still counts manually hidden rows.
• 109 does not.

So on a filtered list the two give identical answers, and every tutorial that only ever demonstrates a filter can tell you they're interchangeable without being caught out. They aren't. The moment someone hides a couple of rows to tidy the view, 9 quietly starts including them again.

Use 109. There is no case where 9 is the safer default.

The shortcut you may already be using without noticing

Filter a list, click the cell below the column, press Alt + =.

Excel inserts =SUBTOTAL(9, ...) rather than =SUM(...). It has done this for years. Most people assume AutoSum always writes SUM and never look at what landed in the cell.

The same applies to a real Excel Table: tick Table Design → Total Row and the totals it creates are SUBTOTAL(109, ...). Tables get this right on your behalf, which is one more argument for using them.

AGGREGATE, and the thing SUBTOTAL cannot do

SUBTOTAL has one hard limit: a single error anywhere in the range breaks it. One #N/A from a lookup and your total is #N/A too.

AGGREGATE is the same idea with an extra argument for what to skip:

=AGGREGATE(function_num, options, range)

options	Skips
5	Hidden rows. Equivalent to SUBTOTAL's 101–111.
6	Error values.
7	Hidden rows and error values. The one you usually want.

=AGGREGATE(9, 7, D2:D21)

That totals the visible rows and steps over any #N/A, #REF! or #DIV/0! sitting in the column.

Treat that as a diagnostic convenience rather than a fix. A total that quietly excludes broken rows is a total that hides how broken your data is — #N/A usually means something specific and deserves an answer, not a bypass.

AGGREGATE also reaches nineteen functions where SUBTOTAL stops at eleven, including LARGE, SMALL, MEDIAN and PERCENTILE.INC. "The third largest order among visible rows" is a one-formula question here and an awkward one anywhere else.

⚠️ AGGREGATE needs Excel 2010 or later, and Google Sheets does not have it at all. If the file travels, stay on SUBTOTAL(109, ...).

Nesting, and why it doesn't double-count

A useful behaviour that is almost never explained: SUBTOTAL ignores other SUBTOTAL results inside its own range.

So you can put a subtotal at the end of each group and one grand total at the bottom covering the whole column, and the grand total will not add the group subtotals on top of the rows they already summarise. It just works, and it is the reason Excel's own Data → Subtotal command produces sane numbers.

AGGREGATE does the same, and with options 0–3 it ignores nested SUBTOTAL and AGGREGATE calls as well.

Three things SUBTOTAL will not do for you

It ignores hidden rows, not hidden columns

Hide a column and every one of these functions still reads it. There is no column equivalent, and there is no option that adds one.

It has no criteria

"Total the visible rows" and "total the rows where Region is North" are different questions. If you want a figure that doesn't depend on what someone filtered five minutes ago, use SUMIFS instead — it reads the data rather than the view.

This is the real decision. SUBTOTAL answers "what am I looking at?", which is right for a working sheet someone is exploring. SUMIFS answers "what is the North total?", which is right for anything that gets printed, sent or presented. Building a report on SUBTOTAL means the numbers change depending on how the last person left the filter.

It won't copy the way you expect

Copy a filtered range and Excel copies visible cells only — helpful, and inconsistent with how formulas behave on the same range. If you want that explicitly, select the range and press Alt + ; (Go To Special → Visible cells only) before copying.

Does this work in Google Sheets?

SUBTOTAL works, and the 1–11 versus 101–111 split behaves the same way. 109 is still the right default.

There is no AGGREGATE. To skip errors, wrap the range instead:

=SUMPRODUCT(SUBTOTAL(109, OFFSET(D2:D21, ROW(D2:D21)-ROW(D2), 0, 1)), --NOT(ISERROR(D2:D21)))

Which is exactly as unpleasant as it looks, and a good reason to fix the errors instead.

When it goes wrong

SUBTOTAL and SUM give the same answer

Nothing is hidden. Either the filter is showing everything, or it was cleared — the funnel icon on the column header tells you which.

The total didn't change when I filtered

You used 9 or 109 on a range that doesn't cover the filtered rows, or the cell is on a different sheet. SUBTOTAL only responds to rows hidden in the same sheet as its range.

It returns #VALUE!

An error inside the range. Find it with =COUNTIF(D2:D21,"#N/A") or switch to AGGREGATE(9, 7, ...) while you investigate.

The grand total is too high

You used SUM for the grand total and SUBTOTAL for the groups, so the grand total counted both. Make the grand total a SUBTOTAL as well and the nesting rule takes care of it.

#NAME? on AGGREGATE

The file is open in Excel 2007 or earlier, in Google Sheets, or in another spreadsheet program that hasn't implemented it.

Manually hidden rows are still being counted

You used 9 rather than 109. This is the whole reason the second numbering exists.

Common questions

Which should I use — SUBTOTAL or AGGREGATE?

SUBTOTAL(109, ...) unless you need to skip errors or want LARGE, MEDIAN or a percentile. Compatibility is the deciding factor, not capability.

Does SUBTOTAL slow a sheet down?

Marginally, and not enough to matter. It has to check the hidden state of every row, so it costs a little more than SUM. On a normal list you will never notice — and if you might, the causes of a slow workbook are elsewhere.

Can I use it with a criterion, like SUMIF?

Not directly. The usual approach is SUMPRODUCT combined with SUBTOTAL over OFFSET, which is hard to read and harder to maintain. Filter the list or use SUMIFS.

Why does my Table total already do this?

Because a Table's Total Row writes SUBTOTAL(109, ...) for you. If your totals have always looked right on filtered data, that's why.

What about COUNT?

SUBTOTAL(102, ...) counts visible numbers, SUBTOTAL(103, ...) counts visible non-empty cells. Putting one of these beside a filtered list — "showing 6 of 20" — is the cheapest sanity check there is.

Does it work on a PivotTable?

PivotTables handle their own subtotals and are not affected by sheet filters in this way. This is a problem for ordinary ranges and Tables.

The short version

• SUM ignores the filter and adds every row, hidden or not.
• =SUBTOTAL(109, range) totals visible rows only.
• 9 and 109 both skip filtered rows; only 109 skips manually hidden ones. Use 109.
• Alt + = below a filtered list already writes SUBTOTAL. Check what's in the cell.
• A Table's Total Row uses SUBTOTAL(109) automatically.
• AGGREGATE(9, 7, range) does the same and skips errors — Excel 2010+, not in Sheets.
• SUBTOTAL ignores nested SUBTOTALs, so group totals never double-count.
• Hidden columns are still counted. There is no option for that.
• For a figure that must not depend on the current filter, use SUMIFS.
• The status bar has been showing you the visible-only total all along.

Next steps: this is the second way a total can be confidently wrong — the first is figures stored as text, which SUM skips without saying so. If you want totals that answer a question rather than describe a view, SUMIFS is the tool, and COUNTIFS does the same for counting. Any figure that reaches a report should also survive the separation between data, calculations and the view.