SUBTOTAL and AGGREGATE: Totals That Follow the Filter and Survive the Errors

Here is a list of ten expense claims with a filter on it. The filter is set to Travel, so four rows are showing. The total at the bottom of the screen says 3,584.45, which is the whole month — travel, software, training and client meals, including the six rows you cannot currently see.

Nothing is broken. SUM was never told about the filter, because SUM cannot be told about the filter. It reads a range of cells, and a hidden cell is still a cell.

The number the reader of that screen expects is 2,046.50, and getting it takes a different function — one of two, in fact, and the choice between them comes down to a question most people have never had to ask: what exactly do you want ignored? Rows that a filter removed. Rows that somebody hid by hand. Cells holding an error. Totals that are themselves totals. Excel has a different answer for each combination, and they live in SUBTOTAL and AGGREGATE.

What you need. SUBTOTAL is in every version of Excel still in use, and in Google Sheets. AGGREGATE arrived in Excel 2010 and is in Excel for the web and on the Mac — but it does not exist in Google Sheets, which is worth knowing before you build a workbook that has to travel. Everything else here is menus and keyboard: Data ▸ Subtotal, the Table total row, and Go To Special.

1) SUM Cannot See the Filter

Put =SUM(E2:E11) under the ten claims and it returns 3,584.45. Filter the Category column to Travel, and it still returns 3,584.45. Filter to something with no rows at all, and it still returns 3,584.45.

This is not a bug and it is not fixable with a setting. Filtering hides rows; it does not delete data. Every function in Excel except SUBTOTAL, AGGREGATE and the pivot-table engine reads hidden cells exactly as it reads visible ones.

Look at the status bar while the Travel filter is on and select column E: Excel says Sum: 2,046.50. That is the number matching what is on screen, and it is sitting an inch below a cell that says 3,584.45. Two correct numbers, one screen, and only one of them prints.

The fix is a single function:

=SUBTOTAL(9, E2:E11)

All ten rows showing: 3,584.45. Filtered to Travel: 2,046.50. Filtered to Software: 329.00. The formula never changes; the filter does.

2) SUBTOTAL's Anatomy: One Number That Chooses the Function

=SUBTOTAL(function_num, ref1, [ref2], …)

The first argument is not a range and not a condition — it is a code that picks which calculation to run. There are eleven of them, and each has a second form that is the same code plus 100:

Nine is SUM, which is why =SUBTOTAL(9, …) is the one everybody half-remembers. Excel will offer you the list as you type the opening bracket, so you do not have to memorise the table — but you do have to know that 9 and 109 are not the same function, and that is the next section.

3) 9 and 109: The Difference Only Appears When Somebody Hides a Row by Hand

Both codes ignore rows removed by a filter. That is the shared behaviour, and for most workbooks it is the only behaviour that ever comes up.

They part company over rows hidden manually — right-click ▸ Hide, or a row height dragged to nothing, or a collapsed outline group:

On the claims list, with the filter set to Travel, both return 2,046.50. Now right-click the C-4104 row — Dan Osei's 1,240.00 — and hide it:

• =SUBTOTAL(9, E2:E11) → 2,046.50. It still counts the row you hid.
• =SUBTOTAL(109, E2:E11) → 806.50. The three travel claims you can actually see.

Which one you want is a question about who did the hiding. A filter is a statement about the data — show me travel — so a total that follows it is reporting a subset somebody asked for. A hidden row is usually a statement about the screen — this row is ugly, this row is a note to myself — and a total that follows that is reporting whatever mood the sheet was in.

Default to 9. Reach for 109 when hiding rows by hand is a deliberate part of how the sheet is used, and write a note next to it saying so, because nobody reading 109 in six months will guess.

One thing neither of them does: hidden columns are invisible to both. Hide column E entirely and every SUBTOTAL over it carries on exactly as before. There is no column equivalent of 109, in any version.

4) The Rule That Stops the Double Count

This is the behaviour that makes SUBTOTAL worth learning even in a workbook with no filters in it at all.

SUBTOTAL ignores any other SUBTOTAL inside its range. SUM does not.

Group the ten claims by category and put a subtotal under each group — Travel 2,046.50, Client meals 258.95, Software 329.00, Training 950.00 — and the block is now fourteen rows: ten claims and four totals. Put a grand total underneath:

SUM adds the ten claims and then adds the four subtotals, which are those same ten claims again. It reports a month that cost 3,584.45 as a month that cost 7,168.90, and it does it silently, with no error and no warning, in a cell that looks exactly like every other total on the sheet.

SUBTOTAL sees the four SUBTOTAL cells in its range and steps over them. That is the entire trick, and it is why the Data ▸ Subtotal command writes SUBTOTAL formulas rather than SUM ones.

If you take one habit from this article: in any block that has group totals in it, the grand total is a SUBTOTAL.

5) Data ▸ Subtotal, and the Sort You Have to Do First

Excel will write those group totals for you. Data ▸ Subtotal opens a three-part dialog: At each change in (the grouping column), Use function (Sum, Count, Average…), Add subtotal to (the columns to total). Tick "Summary below data" or untick it if your house style puts totals on top.

It inserts a labelled row after each group, a grand total at the bottom, all of it as SUBTOTAL(9, …), and an outline down the left margin with buttons 1, 2 and 3: grand total only, groups only, everything.

🎯 Scenario: Ten claims in the order they were submitted, and a manager who wants a total per category.

The trap is one line long, and it is the reason most people give up on this command: sort by the grouping column first. "At each change in Category" means literally that. On the claims in submission order — Travel, Software, Client meals, Travel, Travel, Training… — Excel obeys you exactly and inserts a subtotal every time the value in C is different from the row above. Ten claims in that order produce eight groups out of four categories, each one a "total" of one or two rows.

Two more things worth knowing:

• Removing them is Data ▸ Subtotal ▸ Remove All, not selecting rows and deleting. Deleting by hand leaves the outline behind.
• Copying only what you can see. Select the range, press Alt + ; (Go To Special ▸ Visible cells only), then copy. Without it, a copy of a filtered or outlined range brings every hidden row with it, and you will find them the moment you paste.

6) The Column That Divides by Zero

🎯 Scenario: The same ten claims, and one empty column. Fill F2 with =E2/D2 and copy it down: the cost per day, so travel that ran five days can be compared with travel that ran one.

Two rows have no days in them. C-4102 and C-4108 are software licences — a renewal is not a trip, so D is zero — and dividing by zero gives #DIV/0!. Twice.

That single fact breaks more of this article than the filter does:

SUBTOTAL ignores hidden rows. It does not ignore errors. One bad cell anywhere in the range and the filtered total is an error too, no matter how carefully you set the filter.

Section 11 is the function that fixes it. Before that, two things SUBTOTAL does better than anything else.

7) The Table Total Row Was a SUBTOTAL All Along

Select the claims and press Ctrl + T. Then Table Design ▸ Total Row, or Ctrl + Shift + T. A row appears at the bottom with a dropdown in every cell — Sum, Average, Count, Max, Min, StdDev, Var — and picking Sum writes:

=SUBTOTAL(109, [Amount])

Note the 109, chosen for you, and the structured reference [Amount] instead of E2:E11. Two consequences worth having:

• New rows are included automatically. Type a claim in the row under the last one and the table swallows it; the total row moves down and the formula does not change.
• It follows filters and slicers. Every total on that row responds to the filter dropdowns in the header, so a table plus a total row is a one-click summary with no formula writing at all.

You can point at a table from anywhere else in the workbook the same way:

=SUBTOTAL(109, Claims[Amount])

which totals the visible rows of the Claims table from a summary sheet, and keeps working when the table grows.

8) A Serial Number That Renumbers Itself When You Filter

Typing 1, 2, 3 down a column produces numbers that survive filtering and stop making sense: filter to Travel and you get rows 1, 4, 5, 9. What you usually want is 1, 2, 3, 4 — a count of what is showing.

In G2, and copied down:

=SUBTOTAL(103, $B$2:B2)

The range starts anchored at $B$2 and ends unanchored at B2, so on row 5 it has grown to $B$2:B5. 103 is COUNTA ignoring hidden rows, so each row counts the visible non-empty cells above it including itself. Unfiltered it reads 1 to 10. Filtered to Travel it reads 1, 2, 3, 4 on the four rows still on screen.

The same code, not expanded, counts the filtered rows:

=SUBTOTAL(103, B2:B11)

Ten with no filter, 4 on Travel, 3 on Client meals, 2 on Software. Pair it with the total and a summary line writes itself: 4 claims, 2,046.50.

Use 103 (COUNTA) and not 102 (COUNT) when you are counting text. Claim IDs like C-4101 are text, and =SUBTOTAL(102, B2:B11) counts the numbers among them, which is zero.

9) The Status Bar Tells the Truth and Then Forgets It

Select any range and the bar along the bottom shows Average, Count and Sum of the visible cells. Right-click it and you can add Numerical Count, Minimum and Maximum.

It is the fastest sanity check in Excel: set the filter, select the column, read the number, compare it to the cell your formula is in. If they disagree, your formula is not filter-aware.

It is also not a number you can use. It does not print, it does not refresh into anything, and it disappears the moment you click elsewhere. Check with it; report with a formula.

10) AGGREGATE: Nineteen Functions and Eight Kinds of Ignoring

AGGREGATE is SUBTOTAL with the ignoring made explicit and the function list extended. It has two shapes:

=AGGREGATE(function_num, options, ref1, [ref2], …)     for 1–13
=AGGREGATE(function_num, options, array, k)            for 14–19

The function numbers are the eleven from SUBTOTAL, in the same order, plus eight more:

The options argument is the part with no equivalent anywhere else. It is a number from 0 to 7, and it is a checklist of what to skip:

Two of these eight do almost all the work in practice. 6 — ignore errors — and 7 — ignore errors and hidden rows, which is the filter-aware, error-proof total most people were looking for when they found this article.

11) The Total That Survives a #DIV/0!

Back to column F, with its two #DIV/0! cells.

=AGGREGATE(1, 6, F2:F11)

Function 1 is AVERAGE, option 6 is ignore errors, and the answer is 140.41 — the average cost per day of the eight claims that actually have days. The two licences are not averaged in as zero and they are not averaged in as anything else; they are simply not there.

Compare the three ways this usually gets handled:

The middle row is the dangerous one, because it produces a plausible number. IFERROR(…, 0) does not skip the errors — it replaces them, with a value that then gets averaged like any other. Two zeros among ten drag the answer down by 20%, and nothing on the screen says so.

IFERROR is the right tool when zero is genuinely the answer — a missing sale is zero revenue. It is the wrong tool when the value is unknown or undefined, and "cost per day of a thing with no days" is undefined. Skip it, do not zero it.

To be both filter-aware and error-proof, use option 7:

=AGGREGATE(9, 7, F2:F11)

One caveat on that: AGGREGATE's "ignore hidden rows" means all hidden rows, filtered and manually hidden alike. It behaves like 109, not like 9, and there is no option that ignores only the filter. If that distinction matters to your sheet, SUBTOTAL(9, …) is still the only function that draws it.

12) The Second Personality: LARGE, SMALL and an Array Without Ctrl+Shift+Enter

Function numbers 14 to 19 take a fourth argument, k, and they unlock the part of AGGREGATE that has nothing to do with filters.

=AGGREGATE(14, 6, F2:F11, 1)     → 248.00   the highest cost per day
=AGGREGATE(14, 6, F2:F11, 2)     → 237.50   the second highest
=AGGREGATE(15, 6, F2:F11, 1)     → 64.20    the lowest
=AGGREGATE(12, 5, E2:E11)        → the median claim of the visible rows

=LARGE(F2:F11, 1) cannot do the first of those. It meets a #DIV/0! and returns #DIV/0!. Option 6 is the whole difference.

Then the part that is genuinely unusual: in this second form, AGGREGATE accepts an array expression as its third argument, and evaluates it as an array without Ctrl + Shift + Enter. In a pre-dynamic-array Excel that was close to a superpower, and it still saves a helper column today:

=AGGREGATE(14, 6, (C2:C11="Travel") * E2:E11, 1)

(C2:C11="Travel") is ten TRUEs and FALSEs; multiplied by the amounts it becomes the travel amounts and a lot of zeros; LARGE of that, k=1, is 1,240.00 — the biggest travel claim of the month, with no helper column and no MAXIFS.

The mirror image has a catch. Ask for the smallest the same way and the zeros win: the smallest number in that array is 0, from a row that is not travel at all. Turn the zeros into errors and let option 6 discard them:

=AGGREGATE(15, 6, 1/(1/((C2:C11="Travel") * E2:E11)), 1)

The inner 1/x turns every zero into #DIV/0! and leaves the travel amounts as their reciprocals; the outer 1/x turns them back. Option 6 throws away the errors, and SMALL returns 88.75, the smallest travel claim. It looks like a trick because it is one — but it is the standard idiom, and on a modern Excel you can write =MIN(FILTER(E2:E11, C2:C11="Travel")) instead and read it out loud.

Two rules for 14–19: the k argument is required (leave it off and you get #VALUE!), and k must be at least 1 and no more than the count of values (ask for the 12th largest of ten and you get #NUM!).

13) SUBTOTAL, AGGREGATE, or SUMIFS?

They answer different questions, and picking wrong is how a report becomes untrustworthy rather than merely wrong.

The test is one sentence: if the answer has to be the same tomorrow, when nobody remembers which filter was set, do not let the filter decide it.

A dashboard cell reading "Selected total: 2,046.50" above a filtered list is honest and useful. The same 2,046.50 in a cell labelled "Travel spend, August" is a trap, because the day someone leaves the filter on Software it quietly becomes 329.00 and still says Travel. That cell wants =SUMIFS(E2:E11, C2:C11, "Travel"), which says what it means and cannot be changed by a dropdown.

14) Seven Things That Bite

1. The circular reference. =SUBTOTAL(9, E2:E12) written in E12 includes itself. Excel warns; the fix is to keep the total outside the range, or to let Data ▸ Subtotal place it for you.
2. Grouping counts as hiding by hand. Collapse an outline group (Data ▸ Group) and 109 drops those rows while 9 keeps them. Same for a row height dragged to zero.
3. 102 versus 103. COUNT counts numbers, COUNTA counts anything non-empty. Counting visible claim IDs — which are text — with 102 returns 0, and 0 is a very believable wrong answer.
4. AGGREGATE has no filter-only option. Options 1, 3, 5 and 7 ignore every hidden row, however it got hidden. Only SUBTOTAL(9, …) distinguishes.
5. Nesting is only ignored on options 0–3. =AGGREGATE(9, 6, …) over a block that contains group subtotals will double-count them, because option 6 says nothing about nesting. Use 2 or 3 if the block has totals in it.
6. Hidden columns never matter. Neither function has any notion of a hidden column. If your layout hides columns to make a print view, nothing about your totals changes.
7. Google Sheets has SUBTOTAL and not AGGREGATE. SUBTOTAL behaves the same way there, function codes included. There is no AGGREGATE at all, so an error-skipping total has to be written as SUMIF, FILTER or IFERROR per row — and a workbook that round-trips through Sheets will come back with #NAME? where the AGGREGATEs were.

15) What the Two Functions Are Actually Worth

Three numbers from this one small sheet.

7,168.90 against 3,584.45. A fourteen-row block with four group totals in it, summed with SUM, reports exactly double the money. Nobody notices a doubled total in a month with a big travel claim in it; they notice it in the quarter, when the trend line goes vertical.

140.41 against 112.33. Two #DIV/0! cells in a column of ten. AGGREGATE averages the eight real values. IFERROR(…, 0) averages ten values, two of which were invented, and lands 20% low with no visible sign that anything was skipped.

2,046.50 against 3,584.45. Travel is 57.1% of the month, and one claim — Dan Osei's 1,240.00 — is 34.6% of it on its own. Those two sentences are the entire point of filtering a list, and neither of them can be said out loud until the total at the bottom agrees with the rows on the screen.

None of this is difficult. It is one function argument, chosen once, in the cell everybody reads and nobody checks.

16) Mini Exercises

Use the ten claims above.

1. The baseline. Write =SUM(E2:E11) and =SUBTOTAL(9, E2:E11) side by side, filter to Travel, and write down both numbers. Then filter to a category that does not exist and write them down again.
2. 9 against 109. With the Travel filter on, hide the C-4104 row by hand. Give the two totals and say which one you would put on a printed claim summary.
3. Fill column F. Enter =E2/D2 and copy it down. Then produce, in four cells: the average per day ignoring errors, the highest, the lowest, and the count of rows that produced a real number.
4. The double count. Sort by Category, run Data ▸ Subtotal, then put =SUM and =SUBTOTAL(9, …) over the entire block including the group rows. Explain the difference to somebody who has not read this article.
5. The renumbering serial. Put =SUBTOTAL(103, $B$2:B2) in G2 and copy down. Filter to Client meals and say what G reads on each visible row, and why the last one is 3.
6. Largest by category, no helper column. Use =AGGREGATE(14, 6, (C2:C11="Travel") * E2:E11, 1) and then write the version that returns the second largest travel claim.
7. The zero trap. Change the 14 to a 15 in the formula above and explain the answer you get. Then fix it.
8. Choose the function. For each of these, say whether it should be SUBTOTAL, AGGREGATE or SUMIFS: a cell above a filtered list labelled "Selected"; a cell in a monthly report labelled "Travel"; the total of a column with two #N/A lookups in it; the count of rows currently on screen.

Summary

SUM cannot see a filter, and no version of Excel will ever change that. SUBTOTAL can: the same formula returns 3,584.45 unfiltered and 2,046.50 on Travel, and the only decision is 9 or 109 — filtered rows only, or filtered plus anything hidden by hand.

Its other half is the reason it exists at all. SUBTOTAL steps over other SUBTOTAL cells inside its range, so a grand total over a block of group totals counts the money once. SUM over that same block counts it twice, quietly, in a cell that looks like every other total on the sheet.

What SUBTOTAL cannot do is survive a bad cell. One #DIV/0! in the range and the answer is #DIV/0!, filter or no filter. That is AGGREGATE's job: nineteen functions, an options argument that spells out what to skip, and 6 and 7 doing nearly all of the work — ignore errors, ignore errors and hidden rows. It also gives LARGE, SMALL, MEDIAN and the percentiles an error-proof form, and takes an array expression without Ctrl + Shift + Enter, which is how you get the biggest travel claim out of a mixed column with no helper.

And then the judgement, which is the part no function argument covers. A total that follows the filter is honest on a screen somebody is driving, and dangerous on a page somebody will read next month. If the label says Travel, the formula should say Travel too — that is SUMIFS, and it will still be right when the filter has been changed, the rows have been re-sorted and everyone who built the sheet has moved on.