COUNTBLANK Function

Counts empty cells in a range.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A whole number

Want to practice COUNTBLANK in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

COUNTBLANK counts the empty cells in one range. Cells with a formula that returns empty text ("") also count as blank, but a cell holding 0 or a space doesn't.

Syntax

=COUNTBLANK(range) Explain

Arguments

ArgumentRequired?What it does
rangeYesThe range to check.

Examples

Example 1 — Tasks with no owner

Column B should name who owns each task.

D2fx=COUNTBLANK(B2:B6)
ABCD
1TaskOwnerUnassigned
2Budget reviewAna2
3Supplier audit
4Website refreshCy
5Office move
6Year-end closeEd
=COUNTBLANK(B2:B6) Explain

Example 2 — Is the form complete?

Show a status once every required cell is filled.

=IF(COUNTBLANK(B2:B6)=0,"Complete","Missing "&COUNTBLANK(B2:B6)) Explain

Common mistakes

  • Only one range. COUNTBLANK takes a single range. To check several, add them: =COUNTBLANK(B2:B6)+COUNTBLANK(D2:D6).
  • Spaces aren't blank. A cell with a space looks empty but isn't counted. TRIM the data first.

Notes

  • COUNTBLANK + COUNTA can add up to more than the number of cells, because a formula returning "" counts in both.
  • Related functions: COUNTA, COUNT, ISBLANK, COUNTIF.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.