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).
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.
Counts the number of empty cells in a specified range of cells.
range: The range from which to count the blank cells.
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.
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,
C4 to E4 is the cell reference of the first row of our table.
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.
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.
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
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.
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.
Here we will find the total number of empty cells from our dataset. And we are going to use the below formula
Provided the entire dataset as the range.
This will provide the empty cells from the range we have selected.
- 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 MAX Function in Excel (6 Examples)
- How to Use PROB Function in Excel (3 Examples)
3. Quick Notes
- 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.
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.
- If the cell contains space, then it will be no longer empty.
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.
The function returned 0 after inserting spaces in the cell.
- If a cell contains a formula that returns
“”(empty string), will be counted as a blank cell.
We have set an if formula that produces an empty string as result.
If we use COUNTBLANK for this cell, then the function will count it as blank.
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.
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.