COUNT vs COUNTA vs COUNTBLANK

The short answer to COUNT vs COUNTA: COUNT counts only numbers, while COUNTA counts every cell that isn't empty. COUNTBLANK is the third member of the family and counts the empty cells. Picking the right one matters, because the wrong one gives a believable but wrong number. This guide is for anyone who checks data, builds reports or faces a counting question in an Excel test.

COUNT vs COUNTA vs COUNTBLANK at a glance#

The three functions look at the same cells but count different things. This table shows what each one counts.

All three work in every version of Excel, including Microsoft 365, Excel for the web and Excel on a Mac.

What each function counts#

Each function takes a range and returns a number. COUNT and COUNTA accept several ranges; COUNTBLANK accepts one.

=COUNT(value1, [value2], ...)
=COUNTA(value1, [value2], ...)
=COUNTBLANK(range)

• COUNT answers "how many numbers are there?". Dates and times count, because Excel stores them as numbers.
• COUNTA answers "how many cells have something in them?".
• COUNTBLANK answers "how many cells are empty?".

The COUNTA function guide has more examples. For counting with a condition, such as "how many scores are over 80", you need COUNTIF instead; our overview of the Excel count functions covers the whole family.

The same range counted three ways#

Here is a small list of job applicants. Two scores are missing or not numbers, and two statuses are empty.

Counting the scores in B2:B7#

=COUNT(B2:B7)
=COUNTA(B2:B7)
=COUNTBLANK(B2:B7)

The results are 4, 5 and 1:

• COUNT finds four numbers: 88, 72, 91 and 65. It skips "absent" because that is text.
• COUNTA finds five filled cells: the four numbers plus "absent".
• COUNTBLANK finds one empty cell: B6.

Together they tell a story: six applicants, four real scores, one score that needs fixing and one that is missing.

Counting the statuses in C2:C7#

The status column is all text, so COUNT would return 0 here. That is the classic sign you picked the wrong function.

=COUNTA(C2:C7)
=COUNTBLANK(C2:C7)

COUNTA returns 4 applicants with a status, and COUNTBLANK returns 2 without one (C4 and C7).

Finding entries that aren't numbers#

Subtract COUNT from COUNTA on a number column:

=COUNTA(B2:B7)-COUNT(B2:B7)

The result is 1: the "absent" in B3. On a big import, this one formula tells you whether a column is clean before you total it.

What changes when cells hold formulas#

Reports often copy values with a formula that shows nothing when the source is empty. Say D2 holds =IF(B2="","",B2) and you fill it down to D7. D6 now looks empty, but it holds a formula that returns "", which is empty text.

=COUNT(D2:D7)
=COUNTA(D2:D7)
=COUNTBLANK(D2:D7)

The results are 4, 6 and 1. COUNT still finds four numbers. COUNTA counts all six cells, because every one of them holds a formula. COUNTBLANK counts D6 as blank, because its result is empty text. So COUNTA and COUNTBLANK add up to 7 on a range of only 6 cells.

This isn't a bug: it is how Excel defines "empty" for each function. If you need "how many cells show a value" on a formula column, subtract the blanks from the total number of cells: =ROWS(D2:D7)-COUNTBLANK(D2:D7) returns 5.

When to use COUNT#

Use COUNT when the column should hold numbers and you only want to count real ones. It is the right choice for:

• Counting scores, amounts or quantities that have been entered.
• Counting dates, such as how many orders have a ship date.
• Checking an import. If COUNT is lower than you expect, some numbers may be stored as text.
• Working out a completion rate. =COUNT(B2:B7)/ROWS(B2:B7) divides the four real scores by the six applicants and returns about 0.67. Format it as a percentage to show 67%.

When to use COUNTA#

Use COUNTA when any entry counts, whatever its type. It is the right choice for:

• Counting names, IDs or text labels, such as how many applicants are on a list.
• Counting responses in a column that mixes numbers and text.
• Finding the size of a list that has no gaps, such as a column of order IDs. =COUNTA(A2:A7) returns 6 applicants.
• Counting distinct values in Excel 2021, Excel 2024 or Microsoft 365, with =COUNTA(UNIQUE(B2:B20)).

When to use COUNTBLANK#

Use COUNTBLANK when you want to know what is missing. It is the right choice for:

• Finding gaps before you send a report, such as missing statuses or missing emails.
• Tracking progress, such as how many tasks have no completion date yet.
• Checking formula results that return "" when there is nothing to show, because COUNTBLANK counts those as blank.
• Building a quick warning. =IF(COUNTBLANK(C2:C7)>0,"Missing statuses","Complete") shows Missing statuses until every applicant has one.

Common mistakes with COUNT, COUNTA and COUNTBLANK#

Most wrong counts come from cells that look one way but hold something else:

1. Using COUNT on text. COUNT on a column of names returns 0. Use COUNTA.
2. Numbers stored as text. Imported numbers often sit on the left of the cell and are skipped by COUNT. COUNTA still counts them, so a gap between the two is a warning sign.
3. Formulas that return "". A formula like =IF(B2="","",B2) shows an empty-looking cell. COUNTA counts it as filled and COUNTBLANK counts it as blank, so the two don't add up to the number of cells.
4. Cells with a space. A space looks empty but is text. COUNTA counts it and COUNTBLANK doesn't. TRIM or Find and Replace can clean these.
5. Expecting COUNTBLANK to count zeros. A 0 is a number, not a blank.
6. Counting a header by accident. =COUNTA(A1:A7) includes the "Applicant" header and returns 7, not 6. Start the range on row 2.
7. Expecting COUNT to count TRUE and FALSE. In a range, COUNT skips them, even though Excel can treat TRUE as 1 in maths. To count checkboxes or TRUE results, use =COUNTIF(range,TRUE) instead.
8. Using a fixed range on a growing list. =COUNTA(A2:A7) won't see a 7th applicant added in row 8. Make the range longer than you need, or turn the list into an Excel table so ranges grow with it.

If your counts feed into IF formulas, our comparison of nested IF vs IFS shows the cleanest way to turn them into labels. And to catch errors in the cells you count, see IFERROR vs IFNA. For finding specific words inside text, see FIND vs SEARCH.

Our recommendation#

Use the function that matches your question. Ask "how many numbers?" and use COUNT. Ask "how many entries?" and use COUNTA. Ask "how many are missing?" and use COUNTBLANK. When you check data you didn't create, run all three on the same column: if they don't add up the way you expect, the data has text numbers, stray spaces or formulas returning empty text.

In short: COUNT for numbers, COUNTA for anything filled in, COUNTBLANK for gaps.

Practise it#

Try the exercise on this page: it uses COUNTA and COUNT together to find scores that aren't numbers. Then practise COUNT, COUNTA and COUNTBLANK in a live spreadsheet on a list of job applicants.