In this tutorial, we will learn about Excel COUNTBLANK Function. This function is a member of the Excel COUNT Family but for different use.
- 1 Definition of Excel COUNTBLANK Function
- 2 Excel COUNTBLANK Function – counts cells if empty or not
- 3 Excel COUNTBLANK Function – If formula returns “ “ (empty), considered blank.
Definition of Excel COUNTBLANK Function
Excel COUNTBLANK function counts only blank cells in the data set. If there is any space within the cell, this function will not consider this as a blank cell and do not make a count.
We use this function to check the cell value, whether blank.
It Returns a Value
Excel COUNTBLANK Function returns the number of all blank cells in a data set.
= COUNTBLANK (range)
range: (Required) where to apply the COUNTBLANK function.
Things to Remember
If any cell contains a formula that returns empty, considered as a blank.
A cell contains, text, numbers, errors, #N/A, etc. will not consider as a blank.
If any cell containing zero will not consider as a blank.
Excel COUNTBLANK Function – counts cells if empty or not
In the following example, we have many types in the given range of the data set. Where two cells are appearing as blank. But when we apply the COUNTBLANK function at the bottom of the table, as a result, it returns “1” only.
Which means only one cell is blank and any of these two blank cells, one has some value stored within it.
For checking the value within the cell, we will apply Excel “LEN()” formula (See image below).
This function will count the number of characters within the cell if there is any.
Cells “A3” and cell “A6” are appearing blank in the data set. Any of these cells may contain some character or a value that is not visible.
The length of “(A3)” is zero (0) as shown in the image, which means this cell no value and completely blank.
The length of “(A6)” is one(1) as shown in the image, which means this cell has one character in cell “A6” but not appearing.
So, in this way we can check the value is stored within a cell.
Excel COUNTBLANK Function – If formula returns “ “ (empty), considered blank.
In this example, we will learn that if a formula returns blank result by its condition or criteria. Excel COUNTBLANK Function will consider it as blank.
In the below image we can see that cell “B14” and “B15” has a formula. And because of its criteria or condition, it is not showing the value in the cell “B14”. And due to the same condition or criteria, it is showing the value in cell “B15”.
Cell “B14” has a formula as showing in the next cell “C14” and as its condition is. If the cell value of “A14” is greater than (10) then it will show “YES”. Otherwise, it will show as blank cell as we can see in the cell “B14”.
Now we will change the value of cell “A14” as “11” and then see what happens. See in the below image:-
In the previous image cell “A14” value is equal to “10”, not greater than this, so the result is “Blank”. But when we put the value “11” in the cell “B14”, which is greater than “10”. Then it is showing the result as “YES” as appearing in the formula cell.
Now apply the COUNTBLANK function on both the example given below. When a cell has a formula that returns the result as “0”, it changes its result when a value changes.
See below images below :
In our first image COUNTBLANK function returns its result as “1” because this is containing a formula that returns the result as “0”.
In the second image, the formula is the same but its result is changing now. Because the cell “A28” value has changed from “10” to “11”.
So, with the help of this example we can understand that if any cell contains a formula that returns empty cell value, the Excel COUNTBLANK function considered this as a blank cell.