COUNT, COUNTA and COUNTBLANK: what counts as an empty cell?

Ask what you are trying to count

A facilities team records readings from six rooms. Two readings have been entered, one room has no entry, another says pending, one cell contains a formula that returns empty text, and the last contains a single space. Counting the cells that look filled is not enough to tell how many readings are ready.

Use B2:B7 for the values: 14, 0, a genuinely empty cell, pending, the formula ="", and one space. Column A identifies the rooms. Keep the zero: it is an entered numeric reading, not missing data.

COUNT answers how many values are numbers

COUNT returns 2 for this range: the values 14 and 0. The note pending is not numeric. Neither are the space or the empty text returned by the formula.

This is a count of numeric cells, not a count of valid measurements. An implausible reading would still be a number. If your log has allowed limits, check those separately. Excel dates stored as numeric serial values also count as numbers.

=COUNT(B2:B7)

COUNTA answers how many cells contain something

COUNTA returns 5. Only the genuinely empty cell B4 is excluded. A note, a space and a formula returning empty text all occupy cells, even when their results are not useful readings.

Use COUNTA when an entry of any type matters. Do not use it as proof that every measurement has been completed or that all required answers are meaningful.

=COUNTA(B2:B7)

COUNTBLANK includes a formula that returns empty text

COUNTBLANK returns 2: B4 is empty, and B6 contains a formula whose result is empty text. The zero in B3 does not count as blank. The space in B7 does not count as blank either.

This is why COUNTA and COUNTBLANK are not perfect opposites. B6 is included in both counts: COUNTA sees a cell containing a formula, while COUNTBLANK includes its empty-text result. Adding the two counts gives 7 even though the range has only 6 cells.

=COUNTBLANK(B2:B7)

Inspect an entry that only looks empty

Select a suspicious cell and inspect the formula bar. A formula such as ="" differs from a cell that has never been filled. A space can also be hard to see. Clearing the cell deliberately removes its entry; changing the number format does not.

Do not automatically clear every cell that appears blank. Some formulas intentionally return empty text while waiting for other inputs, and removing them would remove the calculation.

Change a pending note into a reading

Replace pending in B5 with 16. COUNT rises from 2 to 3 because there is now another numeric reading. COUNTA stays at 5: that cell already contained an entry. COUNTBLANK stays at 2.

Next, clear B6, including the formula itself. COUNTA falls to 4. COUNTBLANK remains at 2 because both an empty-text formula and a genuinely empty cell qualify for that particular function. These two edits separate the questions each function answers.

Keep the scope of the count visible

All three formulas inspect the same six-row range. They count cells, not unique rooms, people or completed jobs. If a room appears twice, counting its two readings does not deduplicate it.

For a condition such as readings above a specified limit, use COUNTIF or COUNTIFS and state the condition explicitly. For this audit, keep the three totals labelled Numeric readings, Cells with entries and Blank results so readers know which question each number answers.

Further reading

• Microsoft: COUNT function
• Microsoft: COUNTA function
• Microsoft: COUNTBLANK function