COUNT, COUNTA and COUNTBLANK: counting cells in Excel

In short: =COUNT(B2:B8) counts cells that hold numbers, =COUNTA(B2:B8) counts every cell that is not empty, and =COUNTBLANK(B2:B8) counts the empty ones. Which one you need depends on what the column contains.

Try it now

The team is reporting its October sales. Not every cell holds a figure: a couple of people typed a note, and one cell is still empty. Click the yellow cell and count only the real numbers.

Task The team reports its October sales. Some people typed a note instead of a number, Tom Schulz has not entered anything yet. In E2, count how many sales figures are already there as numbers.

	A	B	C	D	E
1	Name	October sales $			Figures reported
2	Jana Weber	4200
3	Murat Kaya	n/a
4	Lena Hoffmann	3100
5	Tom Schulz
6	Sara Klein	5800
7	Ali Demir	pending
8	Mia Wagner	2700

COUNT vs COUNTA vs COUNTBLANK

Function	Counts	Ignores
COUNT(value1, …)	numbers, including dates and times	text, numbers stored as text, TRUE and FALSE, errors, empty cells
COUNTA(value1, …)	any cell with content: numbers, text, logical values, errors, even "" returned by a formula	only truly empty cells
COUNTBLANK(range)	empty cells and cells whose formula returns ""	cells holding 0 or anything else

COUNT and COUNTA accept several ranges separated by commas, such as =COUNT(B2:B8,D2:D8). COUNTBLANK takes a single range. In the exercise above the answer is =COUNT(B2:B8). COUNTA would also pick up the two notes.

Which one should you use?

Look at what the column is supposed to contain:

• Only numbers are valid, such as sales, quantities or readings: use COUNT. Notes like "n/a" drop out on their own. COUNT is also the number AVERAGE divides by.
• Every entry counts, such as names on an attendee list or customer IDs: use COUNTA. Names are text, so COUNT would return 0.
• You are looking for gaps, such as invoices without a payment date or missing replies: use COUNTBLANK.

If only certain entries should count, say orders from Hamburg, you need a condition, and that is the job of COUNTIF.

Numbers stored as text

Data pasted or imported from another system often arrives with numbers stored as text. They sit on the left of the cell instead of the right, frequently with a small green triangle in the corner. COUNT skips them and SUM does not add them, but COUNTA still counts them, because the cell is not empty.

That gap is useful: =COUNTA(B2:B8)-COUNT(B2:B8) tells you how many entries in a number column are not real numbers. Anything above 0 means your totals are off.

Task The stock list comes from an import. Some quantities sit on the left of the cell: they are stored as text and therefore missing from every total. In E2, count how many quantities in B2:B8 are stored as text. The empty cell does not count.

	A	B	C	D	E
1	Item no.	Stock (units)	Item		Stock stored as text
2	A-114	120	Binder
3	A-108	85	Ballpoint pen
4	A-131	40	Hole punch
5	A-102		Stapler
6	A-127	230	Printer paper
7	A-119	15	Scissors
8	A-105	60	Highlighter

To fix them, select the cells, click the green triangle and choose Convert to Number. If the numbers are mixed with words, such as "120 units", the text functions will get them out.

Counting empty cells with COUNTBLANK

A classic accounts task: one column holds the date an invoice was paid, and an empty cell means it is still open. COUNTBLANK counts exactly those gaps.

Task Column C shows when an invoice was paid. An empty cell means the invoice is still open. In E2, count how many invoices are still open.

	A	B	C	D	E
1	Invoice	Amount $	Paid on		Open invoices
2	R-3104	1250	1/8/2026
3	R-3101	480
4	R-3109	2310	1/19/2026
5	R-3102	760
6	R-3107	1890
7	R-3105	340	1/30/2026
8	R-3103	925	2/7/2026

Keep in mind that a cell holding 0 is not blank and is not counted. A cell whose formula returns "" does count as blank, even though there is a formula in it. That is handy, because helper formulas like =IF(A2="","",…) would otherwise throw the count off.

Common mistakes

COUNT returns 0 on a list of names

COUNT only counts numbers. For names, text or mixed columns you want COUNTA.

COUNTA counts cells that look empty

A formula that returns "" makes a cell look empty, yet COUNTA still counts it. To count only real entries in such a column, use =ROWS(B2:B8)-COUNTBLANK(B2:B8). A space someone typed into a cell is different: it is real content for both functions, and the only cure is deleting it.

The header is inside the range

With COUNTA, a header in row 1 counts as an entry, so the result is always one too high. Start the range in the first data row.

0 used for "nothing"

Typing 0 where nothing is known changes all three results: COUNT counts the 0 as a number and COUNTBLANK no longer sees the cell. Pick one convention per list and stick to it.

COUNT and COUNTA in German Excel

A German Excel uses different names and separates arguments with semicolons:

English	German
=COUNT(B2:B8)	=ANZAHL(B2:B8)
=COUNTA(B2:B8)	=ANZAHL2(B2:B8)
=COUNTBLANK(B2:B8)	=ANZAHLLEEREZELLEN(B2:B8)
COUNTIF	ZÄHLENWENN

Our exercises accept both spellings. You can pick your Excel language below each exercise.

Which Excel versions have COUNT, COUNTA and COUNTBLANK?

All three are available in every current version: Microsoft 365 for Windows and Mac and Excel 2024, 2021, 2019 and 2016, so there is no version to worry about. For a quick look without a formula, select the cells and check the status bar at the bottom right: the Count shown there counts non-empty cells, just like COUNTA.

FAQ