This tutorial describes the formula syntax and usage of the Excel COUNTBLANK Function and its usages.
Definition of Excel COUNTBLANK Function
Excel COUNTBLANK Function is a part of COUNT family in Microsoft Excel. We apply this function to retrieve or count all the blank cells in a range of cells.
It Returns a Value
Excel COUNTBLANK Function returns the number of all blank cells
range: Required. It is a range where to COUNTBLANK function apply
Things to Remember
If any cell contains a formula that returns “ “ (empty), considered as blank.
A cell contains, text, numbers, errors, #N/A etc. will not consider as blank.
If any cell containing zero will not consider as blank.
As we know that Excel COUNTBLANK Function counts only blank cell. In the following image, we have different kind of data. Where two cell is 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 these blank cells has some value in it. For checking the value, we will apply “LEN()” formula (See image below). This function will count the number of characters within the cell.
In the below-given image, apply LEN() formula to find the blank cell in both cells appearing as blank.
Cells “A3” and cell “A6” are appearing blank. It is possible that any of these cells may contain some character or a value which is not visible.
To know that which cell has invisible data and which one is a complete blank. Apply “LEN()” function to know the length of the characters stored in the cell.
The length of “(A3)” is zero (0) as shown in the image, which means this cell has no value and completely blank.
The length of “(A6)” is one(1) as shown in the image, which means this cell has one character, but not appearing.
As a result, the Excel COUNTBLANK Function done its job nicely.
Excel COUNTBLANK Function – Formula returns (” “) empty, considered blank
In this example, we are going to learn about 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 cell value of “A14” is greater than (10) then it will show “YES”. Otherwise will show as blank cell as we can see in the cell “B14”.
Now we will change the value of cell “A14” 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 change the value “11” in the cell “B14”, which is greater than “10”. Then it is showing the result as “YES”, is appearing in the formula cell.
Therefore, the conclusion is Excel COUNTBLANK Function counts the blank cell only in a cell or a range of cells. And in some cases, if there is a formula in a cell which returns its result as blank. Then this function will consider it a blank cell.