How to Use the COUNTBLANK Function in Excel
In this tutorial, you'll learn how to use the Excel COUNTBLANK function to count blank cells.
When you are managing data in Excel, such as the number of people who attended an event or took a test, there may come a time when you want to know how many people were absent.
In Excel, the COUNTBLANK function is a function that counts the number of blank cells that exist in a specified range. In other words, it is a function that can count the number of cells with no value entered.
COUNTBLANK Syntax
The syntax of the COUNTBLANK() function is a follows:
=COUNTBLANK(range)
Argument
- range: The range from which you want to count the blank cells
Remarks
- Cells that contain numbers, text, and errors are not counted.
- Cells with formulas that return empty text are considered blank and will be counted.
- COUNTBLANK will not count the cells with zero value.
Excel COUNTBLANK Examples
Demo data:
| A | B | C | D | E | F | G | |
| 1 | Date | Code | Name | Qty | Price ($) | Amount ($) | Employee |
| 2 | 10/1/2019 | Item1001 | HP Desktop PC 1 | 3 | 225 | 675 | |
| 3 | 10/2/2019 | Item2001 | HP Desktop PC 2 | 2 | 750 | 1500 | John |
| 4 | 10/3/2019 | Item3001 | HP Laptop PC 3 | 2 | 235 | 470 | |
| 5 | 10/4/2019 | Item4001 | HP Laptop PC 4 | 3 | 785 | 2355 |
Example 1
The following example counts the number of cells in cells D2 through G5 and returns 3:
=COUNTBLANK(D2:G5)
As you can see, there are 16 cells in D2 through G5, and three cells are blank.
Example 2
The following example counts the number of blank cells in column C in cells C2 through C5:
=COUNTBLANK(C2:C5)
The above formula returns 0 because there is not an empty cell in the range.
Summary
In this tutorial, you've learned how to use the Excel COUNTBLANK function to count blank cells in Excel. With a few caveats, you can efficiently count blanks with the COUNTBLANK function.
Some functions allow you to restrict the count to some value (other than spaces) or "numbers. Depending on what you want to extract, you can use the many types of COUNT functions.
- If you want to count the number of cells that are not blank, use the COUNTA function
- If you want to count only cells that contain numbers, use the Excel COUNT function
- If you want to count cells that satisfy a single condition, use the Excel COUNTIF function
- If you want to count cells that satisfy multiple conditions, use the Excel COUNTIFS function