Excel COUNT, COUNTA & COUNTBLANK — Count Numbers, Non-Blanks, and Empty Cells

TL;DR — Three functions, three different questions. COUNT(range) counts only cells that hold a number. COUNTA(range) counts every cell that isn't empty — text, numbers, errors, even a formula that returns "". COUNTBLANK(range) counts the empty ones. The trap: when people ask "how many rows have data?" they almost always want COUNTA, but reach for COUNT — and get a number that's silently too low.

=COUNT(B2:B100)       ' how many cells hold a NUMBER (amounts, dates)
=COUNTA(A2:A100)      ' how many cells are NOT empty (the true "row count")
=COUNTBLANK(A2:A100)  ' how many cells are empty (missing data)
=COUNT(B2:B100)/COUNTA(A2:A100)   ' completion rate: numbers entered vs rows

These are three of the most-used functions in Excel, and the difference between them is exactly the kind of thing that produces a dashboard number that looks right and isn't. Pick the wrong one and nothing errors — you just count the wrong thing.

What you'll learn

The one-line difference between COUNT, COUNTA, and COUNTBLANK
Why =COUNT on a column of IDs or names returns 0
What COUNTA really counts (and why it's often more than you expect)
Why COUNTA + COUNTBLANK doesn't always equal the number of cells
How to count numbers, non-blanks, and blanks with confidence

The mental model: three functions answer three "how many" questions

Don't think of these as three flavors of the same tool. Think of them as three different questions you might be asking about a range:

"How many numbers are here?" → COUNT. It ignores text, blanks, logical values, and errors. It sees numbers (and dates and times, which are numbers underneath) and nothing else.
"How many cells are filled in?" → COUNTA. It counts anything that isn't truly empty: text, numbers, errors, spaces, and formula results — including a formula that returns an empty string.
"How many cells are empty?" → COUNTBLANK. The mirror of "filled in," with one important subtlety about "" we'll get to.

Almost every counting mistake comes from asking one question and using the function for another. The most common by far: you want to know how many rows of your table have data (a "how many customers?" question), which is a COUNTA question — but COUNT is the famous name, so it gets typed, and it quietly undercounts.

Why =COUNT returns 0 on your IDs and names

Here's the failure that sends people searching. You have a column of account numbers, invoice IDs, or names, you write =COUNT(A2:A100), and you get 0 or a number far too small. Nothing is broken — COUNT is doing exactly its job.

COUNT only counts real numbers. Two things routinely aren't numbers even when they look like them:

Text — names, codes, and IDs like INV-0042 are text, so COUNT skips them entirely.
Numbers stored as text — digits that came in from a CSV or a system export and sit left-aligned with a little green triangle. They look numeric but COUNT (and SUM) ignore them.

=COUNT(A2:A100)    ' 0  — the column is text IDs
=COUNTA(A2:A100)   ' 99 — this is the "how many rows filled" answer you wanted

So the rule: to count how many rows have an entry, use COUNTA. Use COUNT only when you specifically mean "how many numeric values" — for example, how many rows actually have an amount entered, as opposed to how many rows exist. If you expected COUNT to see numbers and it doesn't, you've also just diagnosed a numbers-stored-as-text problem worth fixing at the source.

What COUNTA really counts (it's more than you think)

COUNTA counts cells that are not empty — and "not empty" is a lower bar than "has meaningful content." Two surprises live here:

A formula that returns "" is counted. =IF(A2>0, A2, "") produces a cell that looks blank but contains a formula returning an empty string. To COUNTA, that cell is not empty (it holds a formula), so it's counted. This is the number-one reason "COUNTA is higher than I expected."
Errors count. A cell showing #N/A or #DIV/0! is non-empty, so COUNTA includes it. A column full of broken lookups still "counts."

=COUNTA(A2:A100)   ' includes cells with "" from formulas, and error values

That's usually fine — but if you're using COUNTA as "how many real entries," be aware it will happily count invisible junk. When you need "how many cells have visible text or a number, ignoring "" formulas," a COUNTIF with a criteria is more precise — =COUNTIF(A2:A100,"<>") behaves differently from COUNTA on "" formulas, and =COUNTIF(A2:A100,"?*") counts cells with at least one visible character.

The gotcha: COUNTA + COUNTBLANK isn't always the cell count

You'd reasonably assume "filled cells + empty cells = total cells," so COUNTA + COUNTBLANK should equal the number of cells in the range. Usually it does — but not when ""-returning formulas are involved, and this is the subtle one:

COUNTA treats a "" formula as non-empty (it counts it).
COUNTBLANK treats a "" formula as empty (it also counts it).

So a cell containing =IF(...,"") is counted by both. In a range with such formulas, COUNTA + COUNTBLANK comes out greater than the number of cells, because those cells are double-counted. There are three distinct states, not two:

State	Example	COUNTA	COUNTBLANK
Truly empty	never typed, or Delete pressed	no	yes
Empty string from a formula	`=IF(A2>0,A2,"")`	yes	yes
Has content	`42`, `"West"`, `#N/A`	yes	no

The practical takeaway: decide what "blank" means for your data before you trust a blank count. If your "empty" cells are really "" formula results, COUNTBLANK counts them as blank while COUNTA counts them as filled — and neither is wrong, they're answering different questions. To count truly empty cells only, =SUMPRODUCT(--(A2:A100="")) counts both real blanks and "", while =ROWS(A2:A100)-COUNTA(A2:A100) counts only the genuinely empty ones.

The judgment call

The whole family collapses to one habit: name the question before you pick the function. "How many rows have data?" is COUNTA — reach for it by default, because real-world key columns are text. "How many numeric values?" is COUNT — use it deliberately, and if it surprises you with a low number, you've found text-stored numbers. "How many are missing?" is COUNTBLANK — but first settle whether a "" formula counts as missing, because that decision, not the function, determines whether your completeness metric is honest. Get those three questions straight and you'll never again ship a count that's quietly off by the size of your text column.

How ExcelMaster helps

Counting bugs are invisible — the number just comes out wrong, with no error to chase. ExcelMaster reads what your column actually contains before it counts: it spots that your "numbers" are text, that some blanks are really "" formulas, and that a stray #N/A is inflating your COUNTA. Then it writes the right function for the question you're actually asking — a true row count, a numeric-entry count, or a missing-data count that treats empty strings the way you intend. Describe what you want to measure; it picks COUNT, COUNTA, or COUNTBLANK correctly and explains why.

Frequently asked questions

What's the difference between COUNT and COUNTA in Excel?

COUNT counts only cells containing numbers (including dates and times). COUNTA counts every cell that isn't empty — text, numbers, errors, and even formulas returning "". To count how many rows have data, use COUNTA; use COUNT only when you specifically want how many numeric values exist.

Why does COUNT return 0 or too few in Excel?

Because COUNT only sees real numbers. If your column is text — names, IDs like INV-0042, or digits imported as text (left-aligned with a green triangle) — COUNT skips them and can return 0. Use COUNTA to count filled cells, and fix the text-stored numbers if you need them to compute.

Does COUNTA count empty cells?

No — but it counts cells that only look empty. A formula returning "" is counted by COUNTA because the cell contains a formula. Truly empty cells (never filled, or cleared with Delete) are not counted. That's why COUNTA is often higher than a visual scan suggests.

Does COUNTA plus COUNTBLANK equal the total number of cells?

Usually, but not always. A cell holding a ""-returning formula is counted by both COUNTA (non-empty) and COUNTBLANK (blank), so in ranges with such formulas the two totals overlap and sum to more than the cell count. Truly empty cells are counted only by COUNTBLANK.

How do I count non-blank cells in Excel?

Use =COUNTA(range) for cells with any content, including "" formulas and errors. For a stricter "has visible content" count that ignores "" formulas, use =COUNTIF(range,"<>") or =COUNTIF(range,"?*") for cells with at least one visible character.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-07.