Counting in Excel
Excel has five counting functions and picking the wrong one is why a count comes back as 0 — COUNT counts numbers only, never text. Point it at a column of names and it returns 0 with no error to explain itself. COUNTA is the one that counts everything non-empty, text included; COUNTBLANK counts the empties; COUNTIF counts cells meeting one condition; COUNTIFS counts cells meeting several at once. Decide what you are counting before you decide what to type: cells holding a number, cells holding anything at all, cells holding nothing, or cells matching a rule.
The formula
=COUNT(A2:A100) numbers only - dates and times qualify, text and blanks do not
=COUNTA(A2:A100) every non-empty cell, whatever it holds
=COUNTBLANK(A2:A100) only the empty cells
=COUNTIF(A2:A100,"Paid") cells meeting one condition
=COUNTIFS(A2:A100,"Paid",B2:B100,">100") cells meeting every condition at once
=SUBTOTAL(103,A2:A100) like COUNTA, but skips rows hidden by a filter
=ROWS(A2:A100) the size of the range itself, filled or notA worked example
A2:A11 holds ten rows: four cells contain a name (Chen, Patel, Diaz, Osei), three contain a number (12, 0 and 45), two contain the text "n/a", and one cell is genuinely empty.
=COUNT(A2:A11), =COUNTA(A2:A11) and =COUNTBLANK(A2:A11) over that same range
COUNT returns 3 — the three numeric cells, and the 0 is counted because 0 is a number rather than a blank. COUNTA returns 9 — every cell except the empty one, so both "n/a" strings are in. COUNTBLANK returns 1. Notice that COUNTA plus COUNTBLANK equals the ten cells in the range, while COUNT plus COUNTBLANK comes to 4: the missing 6 is exactly the text that COUNT silently refused to see.
Which one do I need?
Frequently asked questions
Why does COUNT return 0 when the column is obviously full?
COUNT only counts numeric cells, so a column of names, IDs or statuses contains nothing it recognises and it correctly reports 0 — no error, because nothing went wrong. Switch to COUNTA to count cells of any type, or COUNTIF to count just the ones matching a particular value.
What is the difference between COUNT and COUNTA?
COUNT counts cells containing numbers, which includes dates and times, and skips text, blanks and logical values. COUNTA counts every cell that is not empty, whatever is in it — text, numbers, error values, even a formula whose result is an empty string. On a purely numeric column the two agree; on anything mixed, COUNTA is almost always the one wanted.
Can COUNTA and COUNTBLANK both count the same cell?
Yes, and formula results are where it happens. A cell holding =IF(A1>0,A1,"") looks empty but is not: COUNTA counts it as filled while COUNTBLANK counts it as blank, so the two totals can add up to more than the number of cells in the range. When that distinction matters, use =SUMPRODUCT(--(range<>"")), which treats an empty string as empty.
How do I count only the visible rows in a filtered list?
COUNT and COUNTA include hidden rows, so they ignore the filter completely and the total never changes as you filter. =SUBTOTAL(103,range) counts only visible non-empty cells and =SUBTOTAL(102,range) only visible numbers; both recalculate every time the filter changes.
New guides and tools, once a month
DE + EN · double opt-in · no spam