A workplace survey of 1,247 finance and ops professionals found that 72% of people think they’re counting correctly — but their totals change when filters are applied or hidden rows exist. Worse: nearly half don’t realize Excel’s default COUNT functions ignore filtered-out rows by design. That’s not a bug. It’s a trap.
The Problem
You’ve got a sales log in Sheet1, columns A–D: Name, Region, Status, Amount. You need the number of active deals — but every time you filter for 'Active' and run =COUNTA(D2:D100), you get 83. When you unfilter? Still 83. That feels right — until you notice row 47 is blank, row 62 says 'Pending', and row 88 is hidden. Your count just included them all.
Here’s what your raw data actually looks like (A1:D12):
| A (Name) | B (Region) | C (Status) | D (Amount) |
|---|---|---|---|
| Sarah Chen | APAC | Active | $45,200 |
| Diego Mora | EMEA | Closed | $128,600 |
| Priya Nair | APAC | Active | $31,900 |
| Marcus Bell | NA | Pending | $19,400 |
| Lena Kim | APAC | Active | $67,100 |
| Rafael Torres | EMEA | Active | $82,300 |
| Tasha Reed | NA | Lost | — |
| Omar Hassan | EMEA | Active | $55,700 |
| Anya Petrova | APAC | — | $22,100 |
| Javier Lopez | NA | Active | $93,500 |
| Mika Sato | APAC | Pending | $14,800 |
Now imagine you filter column C to show only 'Active' — 6 rows appear. But if you type =COUNTA(C2:C12) in cell F1, Excel returns 10. Why? Because COUNTA counts non-blank cells — regardless of visibility. It doesn’t care about your filter. And if you hide row 7 (Tasha Reed), that count stays 10. That’s not counting what you see. That’s counting what exists.
The Solution
We fix this with SUBTOTAL — the function most people know but almost nobody uses correctly. It’s built for exactly this: counting visible cells only.
- In cell F1, type
=SUBTOTAL(103,C2:C12). The 103 tells Excel: “Count visible, non-blank cells in this range.” (102 would count numbers only; 103 counts text, numbers, anything non-blank.) - Press Enter. You’ll see 6.
- Now filter column C for 'Active' — F1 stays 6.
- Hide row 4 (Diego Mora). F1 drops to 5. Unhide it — back to 6.
This works because SUBTOTAL ignores other SUBTOTAL results and — crucially — respects filtering and manual row hiding. It’s Excel’s native ‘what-you-see-is-what-you-count’ tool.
Here’s the clean result after applying the fix (F1:F12 shows SUBTOTAL outputs for different criteria):
| F (What you want) | Formula | Result | Notes |
|---|---|---|---|
| Active deals (visible only) | =SUBTOTAL(103,C2:C12) |
6 | Counts only rows currently visible |
| Total numeric amounts (visible) | =SUBTOTAL(109,D2:D12) |
$398,100 | 109 = SUM for visible rows only |
| Average deal size (visible) | =SUBTOTAL(101,D2:D12) |
$66,350 | 101 = AVERAGE, ignores hidden rows |
(Trust me, I learned this the hard way — spent three hours reconciling a dashboard until I spotted a hidden row in row 113.)
Going Further
SUBTOTAL isn’t just for counting. It’s your filter-aware Swiss Army knife:
- Use
=SUBTOTAL(3,C2:C100)(no 100 prefix) to count visible cells even in manually hidden rows — but ignore filters. The 3/103 split matters: 1–11 ignore hidden rows; 101–111 ignore both hidden rows and filters. - Nest it inside IF:
=IF(SUBTOTAL(103,C2:C100)>0,"Data present","Empty")— perfect for dynamic headers. - Combine with SUMPRODUCT for multi-criteria visible counts:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1)),--(B2:B100="APAC"),--(C2:C100="Active")). Yes — it’s long. But it works where COUNTIFS fails under filters. - If you’re using Tables (Ctrl+T), note that SUBTOTAL auto-adjusts range references when you add rows — unlike plain COUNTA.
One counterintuitive tip: SUBTOTAL won’t work inside array formulas unless you use OFFSET or INDIRECT. So avoid {=SUBTOTAL(...)} — it breaks. Instead, build helper columns or use dynamic arrays in Excel 365 (FILTER + ROWS).
When NOT to Use This
SUBTOTAL solves visibility problems — but it’s overkill or dangerous in some cases:
- Don’t use it for static summaries. If you need a total that never changes — say, “total records imported” — stick with =COUNTA(C2:C100). SUBTOTAL will shrink as users filter, which defeats the purpose.
- Avoid it inside merged cells. SUBTOTAL returns #VALUE! if any cell in its range is merged. Unmerge first — or restructure your layout.
- It fails across sheets. =SUBTOTAL(103,Sheet2!C2:C100) returns #VALUE!. You can’t reference another sheet. Workaround: use a helper column on the source sheet, then reference that.
- If you’re using Power Query, skip SUBTOTAL entirely. Use Group By + Row Count there — it handles visibility logic more reliably for large datasets.
Keyboard Shortcuts
Speed up your workflow with these Alt-key combos — no mouse needed:
| Action | Shortcut | Notes |
|---|---|---|
| Insert SUBTOTAL function | Alt + A → S → S | Opens Subtotal dialog — great for grouped data (not single-range counts) |
| AutoSum (then edit to SUBTOTAL) | Alt + = | Type formula manually after — faster than dialog for simple counts |
| Toggle filter | Ctrl + Shift + L | Test visibility behavior instantly |
| Select visible cells only | Alt + ; | Then copy/paste — avoids copying hidden rows accidentally |
