Let’s say, you have a student’s date with their score. However, some students were missing on exam day, and their scores were not on the list, but their names are. So the best way to count the students who were absent is to count the blank cells from the column B.
And, if you want to count cells that are blank in Excel, you can use the COUNTIF and COUNTBLANK which are specifically designed to count cells that are empty (without any value in them).
Using COUNTIF to Count Blank Cells in Excel
You can also use COUNTIF and create a condition to count blank cells. By using the same example, you can follow the below steps to write this formula:
- First, in cell B1, start typing the COUNTIF function (=COUNTIF), and enter starting parenthesis.
- Now, refer to the range A1:A10 from where you want to count the cells with no value.
- Next, in the criteria argument, type “=”. This equals operator tells Excel to count cells where you have no value because with the = operator you have not specified anything.
- After that, type the closing parentheses and hit enter.
And the moment, you hit enter it returns the count for blank cells.
Read Also – Count Cells by Color in Excel
=COUNTIF(B2:B21,"=")
In this formula, COUNTIF checks each cell in the range B2:B21 to see if it matches the criteria specified, which in this case is “=” (means the cell is empty).
You can also use a formula like the following with the “=”&”” criteria. When you use it, it also tells Excel to count only cells with no value in them.
Blank Cells which are Truly Blank
When using the COUNTIF function to count blank cells, it’s important to note that COUNTIF considers cells with spaces to be blank. So, if a cell has a space (or spaces) in it that might look empty to you, COUNTIF will not count it as blank.
To count truly empty cells or contain only spaces, you can make a little changes to the formula:
=COUNTIF(B2:B21, "") + COUNTIF(B2:B21, " ")
This formula has two parts:
- COUNTIF(B2:B21, “”): This counts completely empty cells in the range B2:B21.
- COUNTIF(B2:B21, ” “): This counts cells in the range B2:B21 that contain only a single space.
By adding these two counts, you get the total number of cells that are either empty or contain only spaces.
Custom Formula to Count Blank Cells
You can also use the below code to create custom function in Excel to count blank cells and also ignore all the cells where you have single as well as multiple spaces.
Function CountTrueBlank(rng As Range) As Integer
Dim cell As Range
Dim count As Integer
count = 0
For Each cell In rng
If Trim(cell.Value) = "" Then
count = count + 1
End If
Next cell
CountTrueBlank = count
End Function Use SUMPRODUCT to Count Blank Cells
This formula count blank cells from the range A1:A21 =SUMPRODUCT(--(A1:A21=""))
This formula counts the number of blank cells in the range A1 to A21 by converting empty cells to 1 and non-empty cells to 0, then sum the results.
=SUMPRODUCT(--(LEN(TRIM(A1:A20))=0))
It counts the number of blank cells in the range A1:A21, including cells with only spaces, by trimming spaces and checking if the length is 0, then summing the results using the SUMPRODUCT.
COUNTBLANK to Count Blank Cells
In the following example, you have a few values in the range A1:A10, but a few of the cells are empty. And now, you need to count the cells those cells with no values in them.
You can use the following steps to write this formula:
- First, in cell B1, start typing the COUNTBLANK function (=COUNTBLANK).
- After that, Type the starting parentheses.
- Now, refer to the range A1:A10 in the function.
- In the end, type a closing parenthesis and hit enter.
Once you hit enter it returns the count of the cells that are blank in the specified range.
Use FILTER + COUNTBLANK to Count Blank Cells
With Excel’s dynamic array formulas introduced in recent versions, you can write a formula using the FILTER function. Here’s how you can count blank cells, including those with only spaces, using dynamic array formulas:
=COUNTBLANK(FILTER(A1:A21, TRIM(A1:A21) = ""))
- TRIM(A1:A20) = “”: This part of the formula checks each cell in the range A1 to A20 after trimming spaces. If a cell is empty or contains only spaces, it evaluates to TRUE; otherwise, it evaluates to FALSE.
- FILTER(A1:A20, TRIM(A1:A20) = “”): The FILTER function filters the range A1 to A21 based on the condition specified, returning an array of cells that meet the criteria (empty or with spaces only).
- COUNTBLANK(…): Finally, the COUNTBLANK counts the number of cells in the filtered array that are blank.