Excel COUNTBLANK Function – How to Use (with example)

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

Syntax

=COUNTBLANK (range)

Arguments

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.

 

Example -01

 

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.

 

Excel COUNTBLANK Function

 

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.

 

Excel COUNTBLANK Function

 

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.

 

Example -02

 

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”.

 

Excel COUNTBLANK Function

 

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:-

 

Excel COUNTBLANK Function

 

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.

 

 

Related Post

Excel COUNT Function, Excel COUNTA Function, Excel COUNTIF Function

 

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.