Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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)

Excel COUNTBLANK Function


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:

COUNTBLANK(range)

Arguments:

Argument Required/Optional Explanation
range Required The range from which to count the blank cells.

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.

Using COUNTBLANK Function for Rows in Excel

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.
=COUNTBLANK(C5:E5)

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.

Final output of method to use COUNTBLANK Function for Rows in Excel


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.

Using COUNTBLANK Function for Columns in Excel

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.
=COUNTBLANK(C5:C10)

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.

Final output of method 2 to use COUNTBLANK Function for Columns in Excel


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.

Using COUNTBLANK Function for the Entire Dataset in Excel

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.
=COUNTBLANK(C5:E10)

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.

Final output of method 3 to use COUNTBLANK Function for the Entire Dataset in Excel


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.

COUNTBLANK Function with Excel Table

  • 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.
=COUNTBLANK(Table1[Match 1])

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.
=COUNTBLANK(Table1[#All])

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.

Final output of method 4 to use COUNTBLANK Function with Excel Table


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.
=IF(COUNTBLANK(C5:E5)=0,"Scored in All",COUNTBLANK(C5:E5))

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

Formula Breakdown

  • Here, COUNTBLANK(C5:E5) → It returns the count of blank cells within the range C5:E5.
    • C5:E5 → It is the range argument.
    • Output 1.
  • IF(COUNTBLANK(C5:E5)=0,”Scored in All”,COUNTBLANK(C5:E5)) → It becomes IF(1=0,”Scored in All”,1).
    • Here, the IF function returns Scored in All if the output of the COUNTBLANK(C5:E5) function is equal to 0. Otherwise, it will return the output of the COUNTBLANK(C5:E5) function.
    • Output 1.
  • Now, press ENTER.

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

Using COUNTBLANK Function with Condition in Excel

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


Similar Readings


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.
=COUNTIF(C5:E10, "="&"")

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.

Alternative of the COUNTBLANK Function: Using COUNTIF in Excel

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.
=COUNTIF(C5:E10, "<>"&"")

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.

How to Count Non-blank Cells in Excel

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.


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.

=COUNTBLANK(C5:C10)

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.

=COUNTBLANK(C17:C22)

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(1>2,"Yes","")

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.

=COUNTBLANK(I17:I22)

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.

practice section to use Excel COUNTBLANK Function in Excel


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

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