← All Kits · Excel Kit

COUNT vs COUNTA vs COUNTBLANK in Excel

Michael Nocito · Updated August 2026 · Every figure on this page was run in Excel before it was published

This page tells you which of the three counting functions answers the question you are actually asking, and how to check a count before you put it in front of anyone. The reason it matters: on the twenty-cell column below, COUNT returns 8, COUNTA returns 15, and on the cleaned version of the same column COUNTA returns 20 while COUNTBLANK returns 5. That is 25 answers from 20 cells, and nothing is broken.

What you do: pick by what you mean. Numbers only is COUNT. Anything filled in is COUNTA. Empties is COUNTBLANK. Then run one check, COUNTA plus COUNTBLANK against the size of the range, and read what the gap tells you.

The short version. COUNT counts numbers. COUNTA counts filled cells. COUNTBLANK counts empty ones.

The column the examples run on

Twenty cells, A2 to A21, holding a response column exactly as one arrives from a form export. Numbers, names, a date, some blanks, one cell holding two spaces, one number typed as text, and a TRUE.

Nothing there is unusual. Every one of those cell types turns up in real exported data, and the two that look identical on screen, A2 and A18, are the ones the counting functions disagree about.

The three functions on that column

Before the numbers: how many of the twenty cells do you think COUNT returns?

The eight numbers are 1200, 0, 45.5, 980, the date, 300, 75 and 60. The seven filled cells COUNT skipped are the four names and labels, the two spaces, the text 1200, and TRUE.

Here 15 plus 5 is 20, which is what you want a count to do. That relationship holds until a formula gets involved, which is the next section and the reason this page exists.

Say out loud why COUNT skips A18 while counting A2, when both cells show 1200. The answer is that A18 holds text that happens to look like a number, and COUNT is checking what the value is, not what it looks like.

The cleaned column where 20 plus 5 is 25

First: column B cleans column A with =IF(A2="","",A2) copied down. Twenty cells, five of them showing nothing. What does COUNTA(B2:B21) return?

20. Every cell in B holds a formula, so every cell is filled, including the five that display nothing at all.

Both functions are right, and they are answering different questions. COUNTA asks whether the cell has anything in it, and a formula is something. COUNTBLANK asks whether the cell is empty or shows empty text, and it treats "" as empty on purpose, so the everyday cleaning trick does not litter your reports with false counts.

You can watch the two disagree on one cell. =COUNTBLANK(B4) returns 1, and =ISBLANK(B4) returns FALSE. ISBLANK means "truly empty", and B4 is a formula, so it is not.

This is the specific failure behind a report saying 20 responses when 15 people answered. A cleaning step ran, the column looks the same, and the headline count went up by five with nobody touching the data.

Count what you actually mean

Before the list: which formula counts the cells a human would call filled, ignoring both the empty formulas and the cell holding only spaces?

Two of those deserve a sentence each.

SUMPRODUCT(--(TRIM(A2:A21)<>"")) returns 14 where COUNTA returns 15. The missing one is A10, the cell holding two spaces. TRIM strips the spaces, the result is empty text, and the cell drops out. That is usually the number a person means by "how many did we get".

COUNTIF(A2:A21,"1200") returns 2, counting both the real number in A2 and the text in A18. COUNTIF compares what things look like rather than what they are, which makes it looser than COUNT and occasionally more useful. It is covered alongside its siblings in IF vs COUNTIF vs SUMIF.

Picture your own data for a moment. Take one column you count regularly and ask what is in it that you have never actually looked at: exported blanks, a cleaning formula, a stray total row. The count you publish is a claim about all of it.

The related trap: what SUM does to the same column

=SUM(A2:A21) on that column returns 48,867.50. The amounts add to 2,660.50. The extra 46,207 is the date in A12, because Excel stores a date as the number of days since 1900 and 4 July 2026 is day 46,207.

SUM ignores text, so the names cost nothing, and it ignores the text 1200 too, which quietly leaves 1200 out of a total that should have had it. One stray date inflates the answer, and one number typed as text shrinks it. Both show as clean numbers with no error anywhere. The date behaviour is covered in Excel dates, and the text-number problem in cleaning messy data.

Edge cases worth knowing

Errors count as filled. A cell holding #N/A is counted by COUNTA and skipped by COUNT. It also poisons SUM, unlike text. If a count and a total disagree about which rows exist, look for errors first.

TRUE and FALSE count as filled, not as numbers. COUNTA counts them, COUNT does not, even though they behave as 1 and 0 in arithmetic.

A space is not a blank. A10 holds two spaces. COUNTA counts it, COUNTBLANK does not, and no amount of squinting at the screen will show you which cells are like this. =LEN(A10) returning 2 is how you prove it.

COUNTBLANK takes one range only. COUNT and COUNTA accept up to 255 arguments, so =COUNTA(A2:A21,C2:C21) is fine while the same shape fails on COUNTBLANK. Add two COUNTBLANK calls together instead.

A filtered range still counts the hidden rows. None of the three respect a filter. =SUBTOTAL(103,A2:A21) is the visible-cells version of COUNTA, and =SUBTOTAL(102,A2:A21) is the visible version of COUNT.

Why the split exists

Excel keeps three separate ideas in a cell: whether it is empty, what type its value is, and what it looks like on screen. The three functions each read a different one of those.

COUNT reads the type, so it answers "how many measurements do I have", which is the question behind an average or a total. That is why AVERAGE on this column divides by 8 and returns 6,108.44, using the same rule and the same eight cells.

COUNTA reads emptiness, so it answers "how many rows did somebody fill in", which is the question behind a response rate.

COUNTBLANK reads emptiness and display together, treating a formula's empty text as blank, so it answers "how much is still missing" in the way a person looking at the sheet would.

The three only look interchangeable because on a clean column of pure numbers they agree. Data does not stay clean, and the day it stops, they stop agreeing, which is when the difference reaches a report.

How to apply this to your own work

1. On any column you count, put =COUNTA(range)+COUNTBLANK(range) next to =ROWS(range). Equal is normal. Higher means formulas returning empty text. Lower would mean a range you did not select fully.
2. If COUNT and COUNTA differ by more than you expected, the gap is your text-and-blank problem in one number. Investigate the gap, not the column.
3. Use SUMPRODUCT(--(TRIM(range)<>"")) when the number goes in front of a person. It is the count that matches what they would get by scrolling.
4. Label the count in the sheet with what it counts. "Responses (filled cells)" survives being read six months later. "Count" does not.
5. Do not go back and change every count in every workbook. Fix the ones whose numbers are published, starting with anything anyone reconciles against.

If you have paper nearby, draw twenty boxes and mark each one with what it holds from the table above. Then tick the ones each function counts, one pass per function. Three passes over the same twenty boxes teaches the split faster than any table can.

Cheat sheet

The one habit to keep

Put COUNTA plus COUNTBLANK beside ROWS on any column you report a count from. It takes ten seconds, it needs no judgment, and the one time the three do not agree it will tell you something about your data that the column itself was hiding.

Which number in your current workbook is a count, and would you be able to say out loud, right now, exactly which cells it includes?