Excel COUNTBLANK Function
Summary
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.
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.
Usage notes
Use the COUNTBLANK function to count blank cells in a range, where the word blank means empty. For example, COUNTBLANK(A1:A10) will count the number of blank cells in the range A1:A10.
- Cells that contain text, numbers, errors, etc. are not counted.
- Formulas that return empty text ("") are considered blank and will be counted.
- Cells that contain zero are considered not blank and will not be counted.
COUNTBLANK formula examples
To count the number of cells that are not blank in a range, you can use the COUNTA function. In the example shown, E5 contains this formula:
=COUNTA(B4:B8)
How this formula works
COUNTA is fully automatic. When...
To return a blank result using the IF function, you can use an empty string (""). In the example shown, the formula in D5 (copied down) is:
=IF(B5=1,C5,"")
How this formula works
This formula is based on...
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:
=...
To highlight rows that contain one or more blank cells. you can conditional formatting with a simple formula based on the COUNTBLANK function.
In the example shown, the range B5:F15 is formatted with a conditional...
Related videos
Because tables support structured references, you can learn a lot about a table with basic formulas. In this video, we run through about a dozen examples. It's a nice demo of how formulas and tables work together to save you time.
Related functions
Good links
