Excel trainers say "COUNT counts numbers." That’s not just incomplete — it’s dangerously misleading. COUNT skips dates (even though they’re stored as numbers), ignores text that looks like numbers (like '00123' or '1,234'), and vanishes entirely when you apply AutoFilter. If you’ve ever gotten 0 instead of 12 after filtering a list, you’ve been burned by this silent assumption.
Quick Answer
COUNT only counts cells containing numeric values — meaning true numbers, not numbers formatted as text, not dates (despite their underlying serial numbers), and not logical TRUE/FALSE unless entered directly as numbers (1/0). It ignores empty cells, errors, text, and even filtered-out rows. For everything else, you need COUNTA, COUNTBLANK, or COUNTIF variants.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| COUNT | =COUNT(A1:A20) | Counting only numeric entries (integers, decimals, negatives) | Ignores dates, text-numbers, TRUE/FALSE, filtered rows |
| COUNTA | =COUNTA(B2:B15) | Counting non-blank cells of any type | Counts spaces, apostrophes, error cells (#N/A), and zero-length strings |
| COUNTBLANK | =COUNTBLANK(C1:C12) | Finding truly empty cells or cells with ="" | Counts cells with formulas returning "" — but NOT cells with spaces or non-breaking chars |
| COUNTIF | =COUNTIF(D2:D10,">1000") | Conditional counting with one criterion | Can’t handle multiple OR conditions without array logic or COUNTIFS |
| COUNTIFS | =COUNTIFS(E2:E11,"Paid",F2:F11,">=2024-01-01") | Multi-criteria counting (AND logic) | Each range must be same size; no built-in support for dynamic headers |
| SUBTOTAL(2) | =SUBTOTAL(2,G2:G15) — then filter | Counting only *visible* numeric cells after filtering | Requires manual refresh if filters change; 2 = COUNT, 3 = COUNTA |
Method 1 Deep Dive
Let’s test COUNT with real payroll data in A1:F12:
| Name | Dept | Salary | Start Date | Status | Bonus |
|---|---|---|---|---|---|
| Sarah Chen | Finance | $89,500 | 2022-04-12 | Active | 12500 |
| James Okafor | IT | $112,300 | 2021-09-03 | Active | 18200 |
| Lena Torres | HR | $74,100 | 2023-02-17 | On Leave | 0 |
| Raj Patel | IT | $95,800 | 2020-11-30 | Active | 15700 |
| Maya Dubois | Finance | $82,600 | 2022-07-05 | Active | 11200 |
In column C (Salary), all entries are true numbers — so =COUNT(C2:C12) returns 5. But try =COUNT(D2:D12) (Start Date). Even though Excel stores dates as integers (e.g., 2022-04-12 = 44663), COUNT ignores them. Result? 0. That’s the first surprise: COUNT doesn’t care about date serials. The beauty of this is consistency — it only sees what Excel classifies as Number format, not underlying storage.
Now enter '00123 in C7 (with apostrophe to force text). That cell now looks like a number but isn’t counted. Same for 1,234 — comma formatting doesn’t convert it to numeric. COUNT won’t budge. What makes this elegant is how cleanly it isolates *calculation-ready* values — no guesswork, no coercion.
Method 2 Deep Dive
SUBTOTAL(2) fixes the filtered-row blind spot. Apply AutoFilter to the table above, then filter Dept = "IT". You’ll see two rows. But =COUNT(C2:C12) still returns 5 — it counts hidden rows. Enter =SUBTOTAL(2,C2:C12). Now it returns 2.
The magic number 2 tells SUBTOTAL to use COUNT logic *only on visible cells*. Alt+D+F+F opens the AutoFilter menu instantly — use it often. Pro tip: SUBTOTAL ignores other SUBTOTAL results nested inside the range, preventing double-counting in grouped reports.
Try this counterintuitive test: In an empty column, enter =TODAY() in G2, then copy down to G12. All are valid dates. Now =COUNT(G2:G12) returns 0 — again, because TODAY() outputs a date, not a number. But =COUNTA(G2:G12) returns 11. And =SUBTOTAL(2,G2:G12)? Still 0. So even SUBTOTAL respects COUNT’s strict numeric definition. To count dates, use COUNTA — or convert with =COUNT(--G2:G12) (array-enter with Ctrl+Shift+Enter pre-365, or just Enter in newer versions).
Cheat Sheet
| Goal | Formula | Shortcut / Tip |
|---|---|---|
| Count only numbers (ignore dates, text-numbers) | =COUNT(A1:A100) | No shortcut — but remember: COUNT ≠ “count things that look numeric” |
| Count non-blanks (including errors & spaces) | =COUNTA(B2:B50) | Alt+= auto-sums, then arrow left → changes to COUNTA |
| Count only visible numeric cells after filter | =SUBTOTAL(2,C2:C20) | 2 = COUNT, 3 = COUNTA, 9 = SUM — memorize these three |
| Count cells > $75,000 in Salary column | =COUNTIF(C2:C12,">75000") | Use quotes around criteria; >75000 alone throws #VALUE! |
| Count “Active” Finance staff | =COUNTIFS(E2:E12,"Active",B2:B12,"Finance") | Ranges must align — B2:B12 and E2:E12 both 11 rows |
| Count truly blank cells (not "") | =COUNTBLANK(D2:D12) | If D5 contains =IF(FALSE,"","X"), it’s blank. If it contains a space, it’s not. |
