Excel formula: Count cells that are blank

Generic formula

=COUNTBLANK(rng)

Explanation

To count the number of cells that are blank, you can use the COUNTBLANK function. In the generic form of the formula (above) rng represents a range of cells.

In the example, the active cell contains this formula:

=COUNTBLANK(B4:B8)

How this formula works

COUNTIF counts the number of cells in the range that don't contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted.

You can easily count cells that are not blank using COUNTA function with the same range like so:

=COUNTA(B4:B8)

Author

Dave Bruns

Related formulas

All cells in range are blank

To return TRUE when all cells in a range are blank or empty, you can use a formula based on SUMPRODUCT and a logical test that suits the use case. In the example shown, the formula in E5 is: = SUMPRODUCT ( -- ( B5:D5 <> "" )) = 0 How...

Related functions

Excel COUNTBLANK Function

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

Excel COUNTA Function

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.