How to Use Excel COUNTBLANK Function (5 Ideal 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 function in Excel. At first, you’ll get the overall use of the function within Excel interface. Later, you’ll get the basics and five practical examples of it.

Excel COUNTBLANK Function (Quick View)

Download Practice Workbook

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

Introduction to Excel 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.

Summary:

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

Syntax:

Arguments:

Versions

Workable from Excel 2003.

5 Examples of Using the COUNTBLANK Function in Excel

In this section of the article, we will discuss five practical examples of using the COUNTBLANK function in Excel. Not to mention we used the Microsoft Excel 365 version for this article; however, you can use any version beginning with Excel 2003.

1. COUNTBLANK Function 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. Now, let’s follow the steps mentioned below.

Steps:

To see how many blank games there are, we need to use COUNTBLANK for each of the rows. Let’s start with the first row.

• Now, enter the following formula in the F5 cell.

Here, the C5:E5 cell range refers to the cell reference of the first row of our table.

• After inserting the formula, press ENTER.

In the first row, we have one empty cell. For this reason, it returned 1 in cell F5.

• After that, dragging the Fill Handle will provide the number of empty cells for the rest of the rows, as shown in the following image.

Read More: How to Use Different Types of COUNT Functions in Excel (5 Ways)

2.  COUNTBLANK Function 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 sample dataset contains three companies’ year-by-year share increases over the last few years. Now, let’s use the instructions outlined below.

Steps:

• To see the blank cells in the columns, write the column cell reference within the COUNTBLANK function. For the first column of our table, insert the below formula in C12 cell and press ENTER.

Here, the range C5:C10 indicates the cells of the column named Company 1.

As a result, we have found the number of empty cells in the column.

• Now, drag the Fill Handle to return the blank cells for the other columns as well.

3. COUNTBLANK Function for the Entire Dataset

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

Let’s see an example using the scorer dataset.

Steps:

• Here, we will find the total number of empty cells in our dataset and we are going to use the below formula in the C12 cell.

Here, the range C5:E10 indicates the entire dataset as the range argument.

• Following that, hit ENTER.

This will provide the empty cells from the range we have selected. In this case, it returns to 7, as demonstrated in the following picture.

Similar Readings

• How to Use LINEST Function in Excel (4 Suitable Examples)
• Use CORREL Function in Excel (3 Examples and VBA)
• How to Use MEDIAN Function in Excel (4 Suitable Examples)
• Use Excel MAX Function (6 Useful Examples)
• How to Use PROB Function in Excel (3 Examples)

4. COUNTBLANK Function with Excel Table

In this section of the article, we will learn to use the Table format to count blank cells by using the COUNTBLANK function in Excel. Let’s follow the steps outlined below.

Steps:

• Firstly, select your dataset and press the keyboard shortcut CTRL + T.

• Following that, from the Create Table dialogue box, make sure to check the box of My table has headers option.
• Then, click OK.

As a result, the dataset will be converted into a table as shown in the following image.

• Now, enter the following formula in cell C13.

Here, Table1 is the named range of the dataset, and Match 1 is the named range of the 2nd column of the dataset.

• Next, press ENTER.

Subsequently, you will have the count of blank cells in the column named Match 1.

• After that, to find the total number of blank cells in the entire dataset, use the following formula in cell C14.

Hare, [#All] indicates that the entire dataset is used as the range argument of the COUNTBLANK function.

• Then, hit ENTER.

Consequently, you will have the count of the blank cells of the entire dataset in cell C14 as demonstrated in the following picture.

5. COUNTBLANK Function with Condition in Excel

Counting blank cells with conditions provides us with a tailored option for counting cells that meets specific criteria. In Excel, we can count blank cells with conditions by following some simple steps. These steps are discussed in the following section.

Steps:

• Firstly, use the formula given below in cell F5.

Here, the range of cells C5:E5 indicates the cells of the 3 columns named Match 1, Match 2, and Match 3 respectively.

• Now, press ENTER.

As a result, you will have the count of blank cells for Ronaldo in cell F5 on your worksheet.

• Finally, use the AutoFill option in Excel to get the remaining outputs as shown in the image below.

You can also use various methods mentioned in this article to count blank cells with condition in Excel.

Note: Here, Neymar scored in all 3 matches. That’s why there are no blank cells in the range C7:E7. For this reason, the formula returns “Scored in All” in cell F7.

Alternative to COUNTBLANK: Excel COUNTIF Function

The COUNTIF function is one of the most popular alternatives to the COUNTBALNK function to count the non-blank cells. Now, let’s follow the instructions outlined below.

Steps:

• Firstly, apply the following formula in cell C12.

Here, the range of cells C5:E10 indicates the cells of the columns named Match 1, Match 2, and Match 3.

• After that, press ENTER.

Consequently, you will see the count of blank cells in cell C12 as demonstrated in the image below.

How to Count Non-Blank Cells in Excel

In Excel, we can count non-blank cells by following some simple steps. These steps are discussed in detail in the following section.

Steps:

• Firstly, use the following formula in cell C12.

Here, the range of cells C5:E10 represents the cells of the columns named Match 1, Match 2, and Match 3.

• Following that, press ENTER.

As a result, you will have the count of the non-blank cells within the range C5:E10, by using the COUNTIF function.

By following any of the methods that are described in this article, you can count non-blank cells in Excel quite easily.

Read More: Use COUNTA Function in Excel (3 Suitable Examples)

Things You Should Remember While Using COUNTBLANK Function in Excel

So far, we have provided numbers in our function, but it’s not necessary to set only numbers; you can provide any value, whether numbers or strings. In our range, we have numbers, and our range has 6 cells; 5 of them have numbers, and one is blank.

Here, we used the following formula in cell C12.

The formula returned the number of empty cells as marked in the following image.

If the blank cell contains a space, then it will no longer be empty. So, we inserted a space in cell C21. Now, let’s use the following formula in cell C24.

And now the COUNTBLANK function will consider that cell to be non-empty, and it returned 0 after inserting a space in cell C21.

If a cell contains a formula that returns “” (empty string), it will be counted as a blank cell. We have set an IF formula that produces an empty string as result. The formula is applied in cell I21.

If we use COUNTBLANK for this cell, then the function will count it as blank. Now, let’s apply the following formula in cell I24.

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

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Conclusion

That’s all for today. We have tried showing you how you can use the COUNTBLANK function in Excel. 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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.

Related Articles

• Use Excel RANK Function (6 Ideal Examples)
• How to Use Excel AVERAGEIFS Function (6 Suitable Examples)
• Use SMALL Function in Excel (4 Common Examples)
• How to Use MIN Function in Excel (5 Relevant Examples)
• Find Mean, Median, and Mode on Excel (4 Easy Ways)
• How to Use LARGE Function in Excel (7 Easy Examples)