Excel COUNTBLANK Function – Counting Number of Empty Cells

In our previous blogs, we learned about the COUNT and COUNTA function in excel to count numbers, text, formula errors, etc. in excel. In this blog post, let us go through another counting function called the COUNTBLANK formula in excel. The COUNTBLANK function is used to count the number of blank cells in excel.

As the name itself suggest, the term COUNTBLANK denotes ‘Count the Blanks’. This blog is going to reveal everything that you will be willing to know about the COUNTBLANK function in MS excel.

Here we go 😎

When to Use Excel COUNTBLANK Function 

The COUNTBLANK function in excel is used to find the number of blank cells in excel data. This formula is an excel statistical function which counts empty cells in excel.

What does COUNTBLANK function count?

The excel COUNTBLANK formula counts the number of blank cells within a specified cell range. The function also counts the cells that contain a formula returning an empty or blank string.

What COUNTBLANK function does not count?

This function does not count the cell containing texts, numbers, formula errors.

Syntax and Arguments

=COUNTBLANK(range)

The below point explains the range argument of the COUNTBLANK function:

• range – The first and only argument is a range. In this argument, specify the cell range (group of cells) within which you want to count the empty cells.

Examples of COUNTBLANK Function in Excel

In this section of the tutorial, we would learn some of the examples of using the COUNTBLANK function returning the number of empty cells in excel.

Ex. 1- Simplest Example of COUNTBLANK 

The below image contains some values in column A with blank cells in between.

Suppose you want to count how many cells are blank in a range. To achieve this, simply type the below formula-

  =COUNTBLANK(A1:A5)

As a result, excel would return empty cells count value as 2.

Explanation – In the range A1:A5, there were two empty cells A3 and A4. Therefore, the formula returns 2 as the number of blank cells.

Now, when you have understood how the COUNTBLANK formula works in excel, let us now go some more deep into it.

Ex. 2- Count Blank Cells when Formula Returns an Empty String

In the below image, I have used the excel IF formula to return an empty string when marks are less than 40.

=IF(A2>40,"Yes","")

In the above example image, the formula in cells B2, B5, B6, and B9 returns an empty string (as marks are less than 40).

Let us now check and experiment if the COUNTBLANK function counts the blank cells returned by a formula 😉

Use this formula:

=COUNTBLANK(B2:B10)          

This time the function returns 4.

Thank you for reading 🙂