Why does =COUNT(B2:B100) return zero when you clearly see numbers in that range? Why does =COUNTA(B2:B100) count a cell with just a space as 'non-blank'? Why does your colleague’s =SUBTOTAL(2,B2:B100) give different results after filtering?
Quick Answer
Excel has no single "how many" function — it has 12 distinct counting functions, each designed for a specific data condition: numeric values only (COUNT), non-empty cells (COUNTA), visible rows only (SUBTOTAL), criteria-based counts (COUNTIF, COUNTIFS), text patterns (COUNTIF with wildcards), logical tests (SUMPRODUCT + Boolean), and even array-powered tallies (FILTER + ROWS). The right one depends entirely on what you mean by "how many" — and most people pick the wrong one.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| COUNT | =COUNT(A1:A50) | Counting numbers only — ignores text, blanks, errors | Fails on dates formatted as text (e.g., "2024-03-15" in A5) |
| COUNTA | =COUNTA(B2:B20) | Non-blank cells — includes spaces, formulas returning "", errors | Counts " " (space) as valid — common source of overcount |
| COUNTBLANK | =COUNTBLANK(C1:C15) | Truly empty cells — excludes cells with "" from formulas | Does NOT count cells with invisible characters (CHAR(160)) |
| COUNTIF | =COUNTIF(D2:D30,">1000") | Single-condition counts — >, <, =, text matches, wildcards | Case-insensitive; can’t handle multiple OR conditions natively |
| COUNTIFS | =COUNTIFS(E2:E40,"Paid",F2:F40,">=2024-01-01") | Multi-criteria counts — up to 127 range/criteria pairs | All criteria must be AND logic — no native OR within same column |
| SUBTOTAL | =SUBTOTAL(2,G2:G50) or Alt+M+S+U | Visible rows only — works with filters, outlines, manual hides | Function_num 2 = COUNT; 3 = COUNTA — easy to misselect |
| SUMPRODUCT | =SUMPRODUCT(--(H2:H100="Acme Corp")) | Flexible Boolean counting — handles arrays without Ctrl+Shift+Enter | Slower on >50k rows; double-unary (-- ) trips up beginners |
| ROWS + FILTER (365/2021) | =ROWS(FILTER(I2:I100,I2:I100<>"")) | Dynamic array counting — auto-spills, recalculates on data change | Not available in Excel 2019 or earlier — version lock |
Method 1 Deep Dive
Let’s say you’re auditing sales records in Sheet1, columns A–F:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Sarah Chen | Acme Corp | 2024-03-15 | $45,200 | Paid | 2024-Q1 |
| James Wu | Beta Labs | 2024-03-18 | $32,750 | Pending | 2024-Q1 |
| Maya Patel | Acme Corp | 2024-04-02 | $51,100 | Paid | 2024-Q2 |
| Delta Inc | 2024-04-05 | $28,400 | Paid | 2024-Q2 | |
| Liam Torres | Acme Corp | 2024-04-10 | $63,900 | Pending | 2024-Q2 |
How many Acme Corp deals are marked "Paid"? Don’t reach for COUNTA. That would count all non-blanks — including the blank name in row 4. Instead, use COUNTIFS. In cell H1, type:=COUNTIFS(B2:B100,"Acme Corp",E2:E100,"Paid")
This returns 2 — exactly right. What makes this elegant is its readability: you see both conditions inline, and Excel evaluates them row-by-row, not column-by-column. Bonus tip: if column E contains "paid", "PAID", or "Paid ", add TRIM and UPPER: =COUNTIFS(B2:B100,"Acme Corp",UPPER(TRIM(E2:E100)),"PAID") — but wrap in SUMPRODUCT since array operations need it.
Method 2 Deep Dive
Say you’ve applied an AutoFilter to rows 2–100, hiding all “Pending” entries. You now want to know how many visible rows remain — not the full count. This is where SUBTOTAL shines. In cell I1, enter:=SUBTOTAL(2,D2:D100)
That “2” tells Excel to use COUNT on visible cells only. Try toggling the filter — the result updates instantly. The beauty of this approach is that it respects manual row hides too. But here’s the counterintuitive part: if you use =SUBTOTAL(102,D2:D100), it still counts visible cells — but ignores other SUBTOTAL results nested in the range. That’s useful in grouped reports. Pro shortcut: press Alt+M+S+U to insert SUBTOTAL automatically — Excel will prompt for function number and range.
Now try this: in D2, type ="" (empty string). Then apply filter. COUNT won’t see it — but SUBTOTAL(2) will. Why? Because SUBTOTAL treats formula-blanks as non-blank unless they’re truly empty. So always verify your data hygiene before trusting any count.
Cheat Sheet
| Goal | Formula | Shortcut / Tip | Cell Example |
|---|---|---|---|
| Numbers only | =COUNT(A1:A100) | Use for invoice totals, quantities, scores | A1:A100 |
| Non-blank (incl. spaces) | =COUNTA(B1:B100) | Check for CHAR(160) with =CODE(LEFT(B2,1)) | B1:B100 |
| Visible rows only | =SUBTOTAL(2,C1:C100) | Alt+M+S+U → choose 2 → select range | C1:C100 |
| “Acme Corp” + “Paid” | =COUNTIFS(B2:B100,"Acme Corp",E2:E100,"Paid") | Quotes required for text & operators | B2:B100, E2:E100 |
| Dynamic non-blank count | =ROWS(FILTER(D2:D100,D2:D100<>'')) | Only works in Excel 365 / 2021+ | D2:D100 |
