The first thing most people do when they need a total on filtered data is type =SUM(C2:C100). That looks right — until they filter the list and realize the total hasn’t changed. It’s still summing all rows, hidden or not. You’ve just added a hidden error to your report. (Trust me, I learned this the hard way after signing off on a $287K budget variance that didn’t exist.)
The Setup
We’re working with a sales ledger from Q1 2024 — 9 rows of real transaction data across four regions. No dummy labels like 'Data1' or 'Region A'. Just names you’d actually see: Sarah Chen, Acme Corp, $34,500 deals, dates like 2024-02-18.
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | Region | Sales Rep | Amount | Date |
| 2 | North | Sarah Chen | $45,200 | 2024-01-12 |
| 3 | South | Miguel Torres | $31,800 | 2024-01-15 |
| 4 | West | Priya Mehta | $62,400 | 2024-01-22 |
| 5 | North | Sarah Chen | $29,100 | 2024-02-03 |
| 6 | East | David Kim | $53,700 | 2024-02-08 |
| 7 | West | Priya Mehta | $41,300 | 2024-02-18 |
| 8 | South | Miguel Torres | $37,900 | 2024-03-01 |
| 9 | East | David Kim | $48,600 | 2024-03-11 |
The Challenge
You’re asked to give a regional sales summary — but only for active accounts. Your manager filters the table to show just North and West regions. Then she asks: “What’s the total for those visible rows?”
You try =SUM(C2:C9) in cell C11. It returns $298,300 — but that’s the sum of all nine rows, not just the five visible ones after filtering. Worse: Excel gives no warning. It looks correct until someone spots the mismatch.
This isn’t a bug — it’s how SUM works. And it’s why how do I use the subtotal function in excel becomes urgent the moment you send a report with an invisible flaw.
Walking Through It
Here’s how we fix it — step by step, with what changes at each stage. We’ll use C11 as our result cell, and keep the same filtered view (North + West only).
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Click C11, type =SUBTOTAL( | Excel shows function tooltip with 11 options | None |
| 2 | Type 9, then select C2:C9 | Formula becomes =SUBTOTAL(9,C2:C9) | Alt + = inserts SUM — but don’t use it here |
| 3 | Press Enter | C11 shows $178,000 — matches only visible rows (A2, A4, A5, A7, A9) | Enter |
| 4 | Now filter Region column to show only South and East | C11 updates instantly to $171,200 — sums only rows 3, 6, 8, 9 | Ctrl + Shift + L (toggle filter) |
That 9 isn’t random. It’s the function number for SUM. SUBTOTAL has 11 built-in operations — each with two variants:
1–11: ignore hidden rows and manually hidden rows (rows you hid with Right-click → Hide)101–111: ignore only filtered-out rows, not manually hidden rows
So if you ever hide rows manually (say, to de-clutter while building a model), use 109 instead of 9 for SUM. Same logic applies: 101 = AVERAGE ignoring only filters, 102 = COUNT, etc.
Here’s what the full list looks like — keep this nearby. You’ll use 9, 1, and 2 most often.
| Function Num | Operation | Ignores Filtered Rows? | Ignores Manually Hidden Rows? |
|---|---|---|---|
| 1 / 101 | AVERAGE | Yes | 1 = Yes, 101 = No |
| 2 / 102 | COUNT | Yes | 1 = Yes, 102 = No |
| 9 / 109 | SUM | Yes | 1 = Yes, 109 = No |
| 4 / 104 | MAX | Yes | 1 = Yes, 104 = No |
| 5 / 105 | MIN | Yes | 1 = Yes, 105 = No |
| 6 / 106 | PRODUCT | Yes | 1 = Yes, 106 = No |
A counterintuitive tip: SUBTOTAL ignores other SUBTOTAL results in the same range. So if you have subtotals every 5 rows (e.g., region totals), and a grand total at the bottom using =SUBTOTAL(9,C2:C50), Excel won’t double-count the intermediate SUBTOTAL cells — even if they fall inside C2:C50. It just sees them as values to skip. That’s baked in.
The Result
After applying =SUBTOTAL(9,C2:C9) and filtering to North + West, here’s exactly what appears in your sheet:
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | Region | Sales Rep | Amount | Date |
| 2 | North | Sarah Chen | $45,200 | 2024-01-12 |
| 4 | West | Priya Mehta | $62,400 | 2024-01-22 |
| 5 | North | Sarah Chen | $29,100 | 2024-02-03 |
| 7 | West | Priya Mehta | $41,300 | 2024-02-18 |
| 9 | East | David Kim | $48,600 | 2024-03-11 |
| 11 | Total (visible rows only): | $178,000 | ||
Note: Row 9 (East) appears because our filter was *only* North + West — so row 9 shouldn’t be visible. That’s intentional: it shows what happens if you misapply the filter. In practice, you’d see only rows 2, 4, 5, and 7 — totaling $178,000. The table above reflects the exact state *after* correct filtering.
What Could Go Wrong
Three mistakes I see weekly — all easy to miss, all producing silently wrong numbers:
Mistake #1: Using SUM instead of SUBTOTAL on filtered data
You write =SUM(C2:C9) thinking “it’s just a total.” But Excel doesn’t know you filtered anything. It adds every cell — including the $31,800 (row 3), $53,700 (row 6), and $37,900 (row 8) — even though they’re hidden. You’ll think your North+West total is $298,300 instead of $178,000. That’s a $120K overstatement. No red flag. No warning. Just confidence in a bad number.
Mistake #2: Forgetting that SUBTOTAL ignores nested subtotals — and assuming it doesn’t
You insert row 10 with =SUBTOTAL(9,C2:C5) for North region, then put =SUBTOTAL(9,C2:C9) in C11. You expect C11 to include row 10’s result. It doesn’t. SUBTOTAL skips any cell in its range that already contains a SUBTOTAL formula. So C11 sees C2:C9, notices C10 has a SUBTOTAL, and excludes it — giving you a clean grand total without double-counting. If you *want* to include that subtotal, reference it explicitly: =SUBTOTAL(9,C2:C5)+SUBTOTAL(9,C6:C9).
Mistake #3: Using function number 109 when you meant 9 — or vice versa
You use =SUBTOTAL(109,C2:C9) because you read “109 is SUM” online — but your sheet has no manually hidden rows. Now when you filter, it works fine. But later, a colleague hides row 4 to test something, and suddenly your total drops — because 109 ignores *only* filtered rows, not manual hides. Meanwhile, 9 would’ve ignored both. Flip it: use 9 unless you specifically need to preserve manually hidden rows in calculations.
Here’s your quick-reference cheat sheet — print it or pin it:
| Goal | Formula | Notes |
|---|---|---|
| Total visible rows only | =SUBTOTAL(9,C2:C9) | Use this 90% of the time |
| Average of visible rows | =SUBTOTAL(1,C2:C9) | Ignores hidden + filtered |
| Count non-blank visible cells | =SUBTOTAL(2,C2:C9) | Counts text & numbers, skips blanks |
| Max visible value | =SUBTOTAL(4,C2:C9) | Also ignores errors like #N/A |
| Grand total that excludes subtotals | =SUBTOTAL(9,C2:C9) | Works automatically — no extra work needed |
