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 blank, you can use the COUNTBLANK function. In the example shown, the formula in cell E5 is:
=COUNTBLANK(B5:B14)
Because there are two empty cells in the range, COUNTBLANK...
To run a formula only when one or more cells are not blank, you can use the IF function with an appropriate logical criteria. In the example shown, the formula in E5 is:
=IF(COUNT(C5:C7)=3,SUM(C5:C7),"")...
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 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 simple formulas. In this video, we'll look at some formulas you can use to query a table.
Related functions
