Excel’s COUNTBLANK function counts empty cells in a range, including cells whose formulas return an empty string.
That makes it useful for checking missing entries, but a cell containing a space is not blank. Zero values are not counted either.
In this article, I’ll show you how to count missing cells across columns, compare COUNTBLANK with ISBLANK, and tally missing items in each row.
COUNTBLANK Function Syntax in Excel
COUNTBLANK needs one range to check.
=COUNTBLANK(range)
rangeis required. It is the group of cells where you want Excel to count blanks.
COUNTBLANK includes truly empty cells and cells whose formulas return empty text. It does not count cells that contain zero.
When to Use COUNTBLANK Function
- Count missing assignments in a list.
- Find empty entries across a rectangular block of cells.
- Test how Excel treats formulas, zero values, spaces, and text.
- Count missing items separately for each row.
Example 1: Count Blank Cells in a Column
Let’s start with a single column of assignments.
Below is the dataset with permit IDs and the reviewer assigned to each permit.
I want to count how many permits do not yet have a reviewer.
Here is the formula:
=COUNTBLANK(B2:B9)
Excel checks the eight reviewer cells in B2:B9. Cells B3, B5, and B8 are empty, so COUNTBLANK returns 3.
The permit IDs in column A are outside the range and do not affect the result.
Pro Tip: COUNTBLANK also counts a formula that returns empty text, such as ="". That cell may look empty even though it contains a formula.
Example 2: Count Blanks Across Several Columns
COUNTBLANK can check a rectangular range instead of one column.
Below is the dataset with three inspection checks for eight inspection records.
I want the total number of missing check entries across all inspections.
Here is the formula:
=COUNTBLANK(B2:D9)
The range contains 24 check cells. Sixteen contain “Done,” while eight are empty, so the formula returns 8.
This result counts missing cells rather than incomplete inspection rows. One inspection can contribute more than one blank.
Example 3: COUNTBLANK Versus ISBLANK
Cells that look empty do not always behave the same way in Excel.
Below is a behavior test with an empty cell, an empty-string formula, zero, a single space, text, and a number.
I want to compare how COUNTBLANK and ISBLANK classify each stored entry.
Here is the COUNTBLANK formula entered in C2 and filled down to C7:
=COUNTBLANK(B2)
COUNTBLANK returns 1 for the empty cell in B2 and for the ="" formula in B3. It returns 0 for the other four entries.
Here is the ISBLANK formula entered in D2 and filled down to D7:
=ISBLANK(B2)
ISBLANK returns TRUE only for the genuinely empty cell in B2. It returns FALSE for the empty-string formula because B3 contains a formula.
Zero, a single space, text, and a number are not blank under either test.
Pro Tip: A space is a text character. If a cell contains even one space, COUNTBLANK returns 0 although the cell can look empty on the worksheet.
Example 4: Count Missing Items by Row
The first two examples return one total for an entire range. BYROW can apply COUNTBLANK to each row separately.
Below is the dataset with Packed, Labelled, and Dispatched stages for eight shipments.
I want one missing-stage count for every shipment.
Here is the formula:
=BYROW(B2:D9,LAMBDA(row,COUNTBLANK(row)))
BYROW passes each three-cell row to LAMBDA. COUNTBLANK counts the empty stages in that row and returns one number.
The results are 1, 2, 0, 3, 1, 0, 2, and 0. Excel spills them from E2 through E9.
BYROW and LAMBDA are available in Microsoft 365 and Excel 2024. In older versions, use =COUNTBLANK(B2:D2) and copy it down.
Tips & Common Mistakes
- COUNTBLANK counts cells, not rows. A row with three empty cells adds 3 to the result.
- A zero is a value, so COUNTBLANK does not count it.
- A formula that returns
""is counted by COUNTBLANK, while ISBLANK returns FALSE for the same cell. - Spaces and other invisible text prevent a cell from being counted as blank.
- Use ISBLANK when you need to test whether one cell is genuinely empty.
- Use COUNTA when you need to count cells that contain data, but remember that empty-text formulas complicate a simple blank-versus-nonblank split.
I covered how COUNTBLANK handles columns, rectangular ranges, empty-looking values, and row-by-row counts.
I hope you found this article helpful.
Other Excel articles you may also like:
