When working with an Excel worksheet, it is common to see many blank cells. To figure out the number of blank cells in a range, you can the COUNTBLANK function or the COUNTIF function.
Example: You are working with a dataset with the first name in column A, the last name in column B, and the salary in column C. To count the number of blank cells, we purposely made some empty cells in the dataset.
Formula 1: To count how many blank cells are in column A
= COUNTBLANK(A2:A8)
The result returns 2, and there are two blank cells in column A (in yellow).
Formula 2: To count how many blank cells are in column A
= COUNTIF(A2:A8,"")
Please use only the quotation mark, and leave nothing inside.
The result returns 2, and there are two blank cells in column A (in yellow).
In Excel, the above formulas count cells that are empty strings as blank, and the empty string appears as empty double quotes (""). If you want to exclude these empty string cells, you can use the following formulas:
=COUNTIF(B2:C12,"=")
=ROWS(B2:C12)*COLUMNS(B2:C12) – COUNTA(B2:C12)
Notes: The COUNTIF Function
Formula:
= COUNTIF(Range, "")
Explanations:
– The range is required, which will be the range of cells that you want to count;
– Blank ("") is required.
Cautions:
There is no space in the quotation mark (""), otherwise, you will get errors.
Practice: Count Blank Cells
