Exceljet

Quick, clean, and to the point

Excel COUNTBLANK Function

Excel COUNTBLANK function
Summary 

The Excel COUNTBLANK function returns a count of empty cells in a range. Cells that contain text, numbers, errors, spaces, etc. are not counted. Formulas that return empty text are counted.

Purpose 
Count cells that are blank
Return value 
A number representing blank cells
Syntax 
=COUNTBLANK (range)
Arguments 
  • range - The range in which to count blank cells.
Version 
Usage notes 

The COUNTBLANK function returns a count of empty cells in a range. Cells that contain text, numbers, errors, spaces, etc. are not counted. Formulas that return empty text are counted. COUNTBLANK takes just one argument, range, which must be a cell range.

Examples

In the example shown, the formula in cell E6 is:

=COUNTBLANK(B5:B15) // returns 3

COUNTBLANK returns 3 since there are 3 blank cells in the range. Note that cell B12 is not included because it contains a space character (" "). Cell B13 contains a formula that returns an empty string:

="" // formula in B13

COUNTBLANK considers B13 blank and includes it in the count.

Invisible characters

Some cells look empty, but actually contain invisible characters. To check which cells are blank use Go To > Special > Blanks:

  1. Select a range
  2. Open Go To dialog (Control + G)
  3. Press "Special"
  4. Select "Blanks"

Functions for counting

Notes

  • Cells that contain text, numbers, errors, etc. are not counted.
  • Cells with formulas that return an empty string ("") are counted.
  • Cells that contain zero are not counted.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.