How to Use COUNTBLANK Function in Excel (3 Examples)

Excel provides several statistical functions to help you perform tasks easily and swiftly. Today we are going to show you how to use a statistical function called: COUNTBLANK. For this session, we are using Excel 2019, feel free to use yours (at least 2003).

Overview - Excel COUNTBLANK Function

Practice Workbook

You are welcome to download the practice workbook from the link below.

Excel COUNTBLANK Function

1. Basics of COUNTBLANK Function

The COUNTBLANK function is categorized under STATISTICAL functions in Excel. This function counts the number of empty cells in a given range of cells.

Syntax - Excel COUNTBLANK Function

Summary

Counts the number of empty cells in a specified range of cells.

Syntax

COUNTBLANK(range)

Arguments

range: The range from which to count the blank cells.

Versions

Workable from Excel 2003.

Read More: The Different Ways of Counting in Excel

2. Uses of COUNTBLANK Function

I. COUNTBLANK for Rows

You can use the COUNTBLANK function within rows and the function will return the empty cells from the rows.

To show you examples we have brought a dataset of several athletes and their scores in three games.

Row dataset of COUNTBLANK - Excel COUNTBLANK Function

To see how many blank games, they had we need to use COUNTBLANK for each of the rows. Let’s start with the first row, and our formula will be,

=COUNTBLANK(C4:E4)

C4 to E4 is the cell reference of the first row of our table.

Rows count blank cell

We have found the blank cell. In the first row, we had one empty cell and it returned that.

Similar formula (change of row reference) will provide the number of empty cells for the rest of the rows.

Autofill row count empty

II. COUNTBLANK for Columns

The COUNTBLANK function can be used for the columns as well and then the function will provide the empty cells from the columns.

Our example dataset contains three companies year by year share increase over the last few years.

Columns blank cell dataset - Excel COUNTBLANK Function

To see the blank cells from the columns, write the column cell reference within the COUNTBLANK function.

For the first column of our table, the formula will be

=COUNTBLANK(C4:C9)  

Columns blank cell formula

We have found the number of empty cells from the column. A similar formula for the other columns will return the blank cells for them as well.

Columns blank cell autofill

III. COUNTBLANK for the Entire Dataset

Not only for rows or columns we can use the COUNTBLANK function for the entire dataset.

Let’s see the example using the scorer dataset.

Count blank from entire data - Excel COUNTBLANK Function

Here we will find the total number of empty cells from our dataset. And we are going to use the below formula

=COUNTBLANK(C4:E9) 

Provided the entire dataset as the range. 

Count blank from entire data formula

This will provide the empty cells from the range we have selected.

Count blank from entire data result


Similar Readings


3. Quick Notes

  1. So far we have provided numbers in our function, but it’s not necessary to set numbers only, you can provide any value; numbers, or string.

Text string - Excel COUNTBLANK Function

In our range, we have texts and our range has 3 cells, two have the text, and one is empty. The formula returned the number of empty cells.

Text string output

  1. If the cell contains space, then it will be no longer empty.

Space entry - Excel COUNTBLANK Function

Here we have inserted a couple of spaces in the previously empty cell. And now the COUNTBLANK function will consider that cell to be non-empty.

Space entry result

The function returned 0 after inserting spaces in the cell.

  1. If a cell contains a formula that returns “” (empty string), will be counted as a blank cell.

IF formula - Excel COUNTBLANK Function

We have set an if formula that produces an empty string as result.

IF formula result

If we use COUNTBLANK for this cell, then the function will count it as blank.

COUNTBLANK on formula empty text

 

Here we have set a range of two cells within the function, where one has the if statement and another one is empty. But our function will return 2, as it counts an empty string ("") as blank.

Empty String result

Conclusion

That’s all for today. We have tried showing how you can use the COUNTBLANK function. You can use the function to count the empty cells from rows, columns, or the entire dataset. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your COUNTBLANK function-related scenarios where you have stuck, we are ready to help.


Related Articles

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo