COUNTA vs COUNT vs COUNTBLANK in Excel

The one-line summary

COUNT counts numbers. COUNTA counts anything that isn't blank. COUNTBLANK counts empty cells. Together they let you audit any dataset.

COUNT — numbers only

=COUNT(A2:A100)
-- Counts cells containing numbers, dates, and times
-- Ignores: text, TRUE/FALSE, errors, blank cells
-- Use when: checking how many numeric entries exist

COUNTA — everything non-blank

=COUNTA(A2:A100)
-- Counts any non-empty cell: numbers, text, errors, TRUE/FALSE
-- Ignores: truly empty cells only
-- Use when: counting total entries regardless of data type

⚠️ COUNTA counts "invisible" content

A cell containing a single space looks empty but COUNTA counts it as non-blank. If your count seems too high, check for accidental spaces with =TRIM(A2).

COUNTBLANK — empty cells only

=COUNTBLANK(A2:A100)
-- Counts genuinely empty cells
-- Also counts cells with empty string formulas: =""
-- Use when: finding missing data or gaps in a dataset

Using all three together for data auditing

Total rows:        =ROWS(A2:A100)          = 99
Filled (any):      =COUNTA(A2:A100)        = 87
Numbers only:      =COUNT(A2:A100)         = 82
Text entries:      =COUNTA(A2:A100)-COUNT(A2:A100) = 5
Missing/blank:     =COUNTBLANK(A2:A100)    = 12

Check: COUNTA + COUNTBLANK should equal ROWS

Function	Counts	Ignores
COUNT	Numbers, dates, times	Text, blanks, errors, TRUE/FALSE
COUNTA	Anything non-blank	Empty cells only
COUNTBLANK	Empty cells + "" formulas	Anything with content

💡 Quick data quality check

Run all three on a new dataset: =COUNTA(A:A)-COUNT(A:A) tells you how many cells have text instead of numbers in what should be a numeric column — a quick way to find data entry errors.

Now practise it for real

Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.

Start the Excel Basics track free →

COUNTA vs COUNT vs COUNTBLANKwhich to use?