How to Count Blank and Non-Blank Cells in Excel
How many people have replied? How many orders are missing a delivery date? How many rows have been filled in? Excel has a family of COUNT functions for exactly this, but picking the wrong one gives the wrong answer.
Which function should I use?
| Function | Counts |
|---|---|
| COUNT | Cells containing numbers (including dates) |
| COUNTA | Cells that are not empty: numbers, text, anything |
| COUNTBLANK | Cells that are empty |
| COUNTIF(range, "*") | Cells containing text |
Count filled (non-blank) cells
=COUNTA(A2:A100)
COUNTA counts any cell with something in it: text, numbers, dates, errors, even a single space.
Count empty cells
=COUNTBLANK(A2:A100)
For example, to see how many orders are missing a delivery date.
Count numbers only
=COUNT(B2:B100)
COUNT ignores text and blanks. Dates count as numbers.
Count text only
=COUNTIF(A2:A100, "*")
The asterisk is a wildcard meaning "any text", so this counts cells containing text but not numbers.
Count non-blank cells with a condition
To count how many people in the North region (column B) have an email address (column C):
=COUNTIFS(B2:B100, "North", C2:C100, "<>")
"" means "not blank".
The empty-looking cell problem
Sometimes COUNTA gives a higher number than you expect. That's usually because some cells look empty but aren't:
• A formula that returns "", like =IF(A2="", "", A2*2). It shows nothing, but COUNTA counts it.
• A space typed into a cell by mistake.
• An apostrophe on its own.
• A space typed into a cell by mistake.
• An apostrophe on its own.
COUNTBLANK, on the other hand, counts cells with "" as blank. So COUNTA and COUNTBLANK don't always add up to the total number of cells.
To count cells that genuinely show something, ignoring formulas that return "":
=SUMPRODUCT(--(LEN(A2:A100)>0))
Quick check without a formula
Select the cells and look at the status bar at the bottom of the window. Count shows the number of non-empty cells (like COUNTA), and you can right-click the status bar to add Numerical Count (like COUNT).
Highlight the blanks
To see where the gaps are, select the range and go to Home > Find & Select > Go To Special > Blanks > OK. Every empty cell is selected. Give them a fill colour, or type a value and press Ctrl + Enter to fill them all at once.
Troubleshooting
COUNT returns 0. The numbers are stored as text. Look for left-aligned numbers or green triangles.
COUNTA counts too many. Look for spaces or formulas returning "". Use the SUMPRODUCT formula above.
COUNTBLANK counts too few. Cells containing a space aren't blank. Use Find and Replace to remove stray spaces.
Wrapping up
COUNTA for filled cells, COUNTBLANK for empty ones, COUNT for numbers and COUNTIF(range, "*") for text. If the numbers look off, the culprit is usually a cell that only looks empty.
