Statistics · Head-to-head

COUNT vs COUNTA

Both count cells, but they count different things. COUNT counts only cells holding numbers (dates and zero included); COUNTA counts every cell that is not empty — text, logical values, errors and even empty strings. The single letter "A" means "all". Below, the same mixed range is counted by both, live.

fx
COUNT(…)  vs  COUNTA(…)
3 vs 6
Mixed-range count
0 / 1
On a text cell
1 / 1
On a zero cell
0 / 1
On an empty ""
▶ Try both live See the feature matrix
🏆Verdict: Different questions, not better/worse

COUNT answers 'how many numbers?' (3 on the mixed range); COUNTA answers 'how many filled cells?' (6). Use COUNT for an average denominator, COUNTA for completion and non-blank checks.

COUNT strengths

  • Counting numeric entries (sales amounts, scores, readings).
  • Building the denominator of an average by hand.
  • Counting dates (stored as numbers).
  • You want stray text labels ignored.

COUNTA strengths

  • Counting filled rows / completed records.
  • Counting non-blank IDs or names (text).
  • Counting cells that may hold text or logical flags.
  • Attendance or presence checks where any value counts.

Feature-by-feature matrix

What each function counts on the same mixed range.

CapabilityCOUNTCOUNTAWhy it matters
Counts numbers✓ Numeric cells✓ Numeric cellsBoth count 10, 0, 30.
Counts text✗ Ignored✓ CountedThe core difference: 'hello' adds 1 to COUNTA only.
Counts logical TRUE/FALSE✗ Ignored✓ CountedTRUE adds 1 to COUNTA, 0 to COUNT.
Counts empty string ""✗ Not a number✓ Non-emptyA formula "" counts in COUNTA (measured 1).
Counts a zero✓ Zero is a number✓ Non-emptyNeither skips 0.
Skips truly blank cells✓ Blank skipped✓ Blank skippedOnly a genuinely empty cell is skipped by both.
Average-denominator safe✓ Matches AVERAGE✗ Inflates denominatorSUM/COUNTA understates the mean when text is present.

Syntax, side by side

Same argument shape; the difference is the counting test.

COUNT

COUNT(value1, [value2], …)
value1A range or list of values to count numerically.
value2…Optional extra ranges or values.
=COUNT(B2:B7) → 3

COUNTA

COUNTA(value1, [value2], …)
value1A range or list of values to count if non-empty.
value2…Optional extra ranges or values.
=COUNTA(B2:B7) → 6

Live head-to-head playground

One editable mixed column, two counters. Pick a scenario or type your own — both re-evaluate live so you see exactly which cells each counts.

A1Sample data — click any cell to edit; both formulas re-evaluate live

COUNT

—

COUNTA

—
HyperFormula 3.5 · live

Every difference, measured

These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.

Mixed range (numbers, text, zero, empty-string, logical)

One range holding numbers, text, a zero, an empty string and a logical value.
COUNT
=COUNT(B2:B7)
3
COUNTA
=COUNTA(B2:B7)
6
Three numbers (10, 0, 30) → COUNT 3. Six non-empty cells (those plus "hello", "" and TRUE) → COUNTA 6.

Numeric-only selection (B2, B6)

A selection containing only the numeric cells, so both functions agree.
COUNT
=COUNT(B2,B6)
2
COUNTA
=COUNTA(B2,B6)
2
Selecting only the numeric cells (10 and 30): both agree at 2. The divergence only appears when non-numbers are in the selection.

A single text cell

A single cell holding text — the core COUNT vs COUNTA divergence.
COUNT
=COUNT(B3)
0
COUNTA
=COUNTA(B3)
1
A text cell ("hello"): COUNT 0, COUNTA 1. This is the core difference.

A single logical cell (TRUE)

A single TRUE cell: a logical value, not a number.
COUNT
=COUNT(B7)
0
COUNTA
=COUNTA(B7)
1
TRUE is a logical value, not a number: COUNT 0, COUNTA 1.

An empty-string cell ("")

A cell holding "" (often from a formula) — non-empty to COUNTA.
COUNT
=COUNT(B5)
0
COUNTA
=COUNTA(B5)
1
An empty string "" is non-empty to COUNTA (1) but not a number to COUNT (0). A truly blank cell would be 0 for both.

A zero value

A cell holding 0 — a real number both functions count.
COUNT
=COUNT(B4)
1
COUNTA
=COUNTA(B4)
1
Zero is a number: both count it (1 and 1). COUNT does not skip zeros.

A single number

A single numeric cell, where both agree.
COUNT
=COUNT(B2)
1
COUNTA
=COUNTA(B2)
1
A single number agrees: 1 and 1.

Engine benchmark

Engine cost on a mixed numeric/text column (relative only, not Microsoft Excel).

Table sizeCOUNTCOUNTA
1,000 rows × 200 lookups81.66 ms68.5 ms
5,000 rows × 200 lookups593.88 ms570.62 ms
10,000 rows × 200 lookups1986.03 ms1953.48 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Both functions scan the same range once; absolute times are engine-specific and only meaningful as a relative signal.

Which one should you use?

Pick by the question you are asking.

SituationUseWhy
Count numeric sales amountsCOUNTText headers and labels are ignored.
Count completed rows (any value)COUNTAA row with text or a flag still counts as filled.
Count cells containing zeroEitherZero is a number: both count it.
Count non-blank text IDsCOUNTACOUNT returns 0 on text; COUNTA counts each ID.
Count TRUE/FALSE flagsCOUNTACOUNT ignores logical values; COUNTA counts them.
Denominator for a manual averageCOUNTMatches AVERAGE, which uses only the numbers.

COUNT → COUNTA migration

Common counting tasks, side by side.

TaskCOUNT / old wayCOUNTAWhat changed
Average denominator=SUM(B2:B7)/COUNT(B2:B7)=SUM(B2:B7)/COUNTA(B2:B7)COUNTA version understates the mean when text is present.
Completion rate=COUNT(B2:B7)/ROWS(B2:B7)=COUNTA(B2:B7)/ROWS(B2:B7)COUNTA counts any filled row as done.
Non-blank ID audit=COUNT(IDs)=COUNTA(IDs)A gap means some IDs are stored as text.
Count blanks=COUNTBLANK(B2:B7)=ROWS(B2:B7)-COUNTA(B2:B7)COUNTA treats "" as non-empty; COUNTBLANK does not.

Frequently asked questions

What is the difference between COUNT and COUNTA?
COUNT counts cells that contain numbers (including dates and zero). COUNTA counts every non-empty cell, including text, logical values, errors and empty strings. On a mixed range they can differ sharply — measured here as 3 vs 6.
Does COUNT count zero?
Yes. Zero is a numeric value, so COUNT counts it (measured: 1). Only truly blank cells and non-numeric values are ignored.
Does COUNTA count empty strings ("")?
Yes. A cell holding "" (often from a formula) is non-empty to COUNTA, so it counts as 1 (measured). A genuinely blank cell is not counted. Use COUNTBLANK to count blanks.
Which should I use for an average denominator?
Use COUNT, because AVERAGE itself uses only the numbers. Dividing SUM by COUNTA would give a wrong (lower) mean whenever text cells are present.
Do COUNT and COUNTA count TRUE/FALSE?
COUNT ignores logical values (measured: 0 on TRUE). COUNTA counts them (measured: 1). If you need logical values treated as 1/0 in a calculation, use AVERAGEA or coerce with --.