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 the Excel interface. Later, you’ll get the basics and five practical examples of it.
Excel COUNTBLANK Function (Quick View)
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.
COUNTBLANK Function in Excel: 5 Examples
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.
=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.
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.
=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.
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.
=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.
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 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.
5. COUNTBLANK Function with Condition in Excel
Counting blank cells with conditions provides us with a tailored option for counting cells that meet 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.
- 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 conditions 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.
Read More: COUNTBLANK Not Working in Excel
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.
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.
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)
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 a 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.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
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 are stuck; we are ready to help.
<< Go Back to Excel Functions | Learn Excel
