Guide Formulas
My row count is wrong and I do not know which COUNT to use
Excel has five counting functions and they disagree with each other on purpose. The disagreements are useful — the gap between two of them is often the fastest diagnostic you have.
COUNT counts numbers only. COUNTA counts
anything non-empty, including text and empty strings. COUNTBLANK counts empty cells
and empty strings. COUNTIF and COUNTIFS count what matches your
conditions. If COUNT is lower than you expect on a numeric column, the missing
cells are numbers stored as text.
The gaps are the useful part
Most guides present these as five ways to do the same thing. They are more useful as instruments: the difference between two of them tells you something about the data that neither tells you alone.
COUNTA−COUNT= how many numeric-looking cells are actually text. On a column that should be all numbers, this should be zero.COUNTBLANK− genuinely empty cells = how many empty strings are sitting in the range from formulas returning"".COUNTA−COUNTIF(range,"<>")= the same measure from the other direction.
Running the first of those on any imported column takes five seconds and finds the
“SUM is wrong” problem before it reaches a report.
| What you see | What actually happened | What fixes it |
|---|---|---|
COUNT lower than expected | Some cells are numbers stored as text | Convert them, or count with COUNTA |
COUNTA higher than the visible rows | Empty strings from formulas are being counted | Use COUNTIF(range,"<>") |
COUNTBLANK higher than expected | It counts empty strings as blank | That is intended — the gap is diagnostic |
COUNTIF returns 0 | The criterion does not match exactly | Check for spaces; try a wildcard |
| Counts change when you filter | They do not — use SUBTOTAL for that | SUBTOTAL(103,...) counts visible rows |
How to choose the right counting function
Counting numbers: COUNT
=COUNT(A2:A500) counts numeric cells and nothing else. Dates count,
because dates are numbers. Text does not, even if it looks numeric.
Counting anything present: COUNTA
=COUNTA(A2:A500) counts everything that is not empty — including
error values and including empty strings from formulas, which is the one to watch.
Diagnose a numeric column with the gap
The most useful five seconds you can spend on an imported column:
=COUNTA(A2:A500)-COUNT(A2:A500)Zero means the column is genuinely all numbers. Anything else is the count of cells
SUM is silently ignoring.
Counting by condition: COUNTIF and COUNTIFS
One condition or many:
=COUNTIF(C2:C500,"North")
=COUNTIFS(C2:C500,"North",D2:D500,">1000")Wildcards work in the criterion: "*Ltd" matches anything ending in Ltd,
"?????" matches any five characters. To match a literal asterisk or question mark,
prefix it with a tilde: "~*".
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
The five, in one place
COUNT— numbers only. Dates included, text excluded.COUNTA— anything not genuinely empty, including errors and empty strings.COUNTBLANK— empty cells and empty strings.COUNTIF— one range, one criterion.COUNTIFS— many ranges, many criteria, all of which must hold.
Note that COUNTA and COUNTBLANK both count empty strings, so on a range
containing them the two will sum to more than the number of cells. That is not a bug; it is
the two functions disagreeing about what an empty string is.
Counting unique values
None of these does it. On Microsoft 365 use =COUNTA(UNIQUE(A2:A500)); on older
versions use the SUMPRODUCT/COUNTIF pattern. Google Sheets has
COUNTUNIQUE, which Excel does not.
Counting visible rows only
All five ignore filters. For a count that respects them, use =SUBTOTAL(103,A2:A500),
which is COUNTA restricted to visible rows.
Why COUNTIF returns zero when the value is there
Same reason lookups fail: the criterion has to match the stored value exactly. Trailing spaces, non-breaking spaces and a number-versus-text mismatch all produce a confident zero.
Questions people ask
What is the difference between COUNT and COUNTA?
COUNT counts only cells containing numbers. COUNTA counts every cell that is not genuinely empty, including text, errors and empty strings.
Why is my COUNT lower than the number of rows?
Some of the cells are numbers stored as text, which COUNT does not count. The difference between COUNTA and COUNT tells you how many.
Does COUNTBLANK count formula blanks?
Yes. COUNTBLANK counts both genuinely empty cells and cells containing an empty string returned by a formula.
How do I count unique values?
None of the COUNT functions does it. Use =COUNTA(UNIQUE(range)) on Microsoft 365, or the SUMPRODUCT and COUNTIF pattern on older versions.
Why does COUNTIF return zero when I can see matching values?
The criterion must match the stored value exactly. Trailing or non-breaking spaces, or a mismatch between text and numbers, all cause it to find nothing.
How do I count only the rows a filter left visible?
Use =SUBTOTAL(103,range). The COUNT functions themselves have no awareness of filters.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.