COUNT, COUNTA and COUNTBLANK functions in Excel

COUNT counts cells that hold numbers, COUNTA counts cells that are not empty, and COUNTBLANK counts empty cells.

Counting sounds too simple to need three functions. But "how many?" hides a question: how many what? Cells with numbers, cells with anything in them, or cells with nothing? Choosing the wrong counter is one of the commonest quiet errors in a spreadsheet.

Syntax

=COUNT(value1, [value2], ...)

=COUNTA(value1, [value2], ...)

=COUNTBLANK(range)

Argument	What it is
`value1, value2, ...`	Cells or ranges to count in.
`range`	For COUNTBLANK: one range.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=COUNT(D2:D9)
Result: 7
Cells with a number in the Science column. One is empty, so 7.
=COUNTA(D2:D9)
Result: 7
Cells that are not empty: also 7 here.
=COUNTBLANK(D2:D9)
Result: 1
The empty cells: 1.
=COUNT(A2:A9)
Result: 0
The names are text, not numbers, so COUNT finds none.
=COUNTA(A2:A9)
Result: 8
COUNTA counts text too: 8 names.
=COUNTA(A2:A9)-COUNT(D2:D9)
Result: 1
Students on the list minus marks entered: how many Science marks are still missing.
=COUNT(C2:E9)
Result: 23
A block: 24 cells, of which one is empty.

The three counters

Function	Counts
COUNT	cells that contain numbers (dates and times are numbers too)
COUNTA	cells that contain anything: numbers, text, TRUE or FALSE, errors
COUNTBLANK	cells that are empty

The A in COUNTA can be read as "all" or "anything".

Which one do I want?

How many people are on the list? Count the names, which are text: COUNTA.
How many marks have been entered? Count the numbers: COUNT.
How many are still missing? COUNTBLANK, or the difference between the first two.

Using COUNT on a column of names gives 0, as the fourth example shows. Using COUNTA on a column of amounts counts a stray note or heading as if it were a sale.

Cells that look empty and are not

COUNTA is stricter about "empty" than your eyes are. It counts:

a cell containing only a space, typed by accident;
a cell with a formula that returns empty text "".

COUNTBLANK, on the other hand, treats a formula returning "" as blank. So for the same range, COUNTA plus COUNTBLANK can come to more than the number of cells. If COUNTA gives a higher figure than you expect, look for cells holding a lone space. The TRIM function and Find and Replace can clear them.

Numbers stored as text

COUNT skips numbers that are stored as text, just as SUM does. If COUNT gives fewer than you can see, that is the reason. Comparing COUNT(range) with COUNTA(range) is a quick test: when they differ on a column that should be all numbers, something in it is not a real number.

Counting with a condition

These three count everything of a kind. To count only the cells that meet a condition, such as marks of 33 or more or rows for one class, use COUNTIF and COUNTIFS.

Uses

The size of a list that keeps growing. =COUNTA(A:A)-1 counts the entries in column A, less the heading.

The last value in a column. =INDEX(A:A,COUNTA(A:A)) returns the last filled cell, provided there are no gaps.

A progress figure. =COUNT(D2:D9)/COUNTA(A2:A9) is the share of students whose marks are in. Format it as a percentage.

Common mistakes

COUNT on a text column, giving 0.
COUNTA including a heading or a total row.
Believing a cell is empty when it holds a space.
Expecting any of the three to skip hidden rows. They count everything. SUBTOTAL with code 2 or 3 counts only visible rows.

In Google Sheets

COUNT, COUNTA and COUNTBLANK work the same way in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.