How to Count cells that are blank?
We can count cells that are blank in excel by using COUNTBLANK().
What is COUNTBLANK?
The COUNTBLANK() function in excel, count cells that are blank. Its function is to count the number of cells in a given range such that the cells selected can either contain no data or contain a formula that returns null.
“rng” in the formula represents the range of the cells.
How to use the Formula to Count cells that are blank?
Figure 1. Example 1 of COUNTBLANK() to Count cells that are blank
In this example, you have a small table containing students information. To apply COUNTBLANK() on this table:
- Select any column, where you want to display a count for a particular range. In this case, it is K9.
- In the function box, type
- Give the arguments i.e. the range. For K9, assign the range D9:D16. The count came out to be 4.
- You can observe the same in the table as well. Look at columns from D14:G14, they are blank. So, the function returned the correct number.
Alternative Using COUNTIF()
Figure 2. Example 2 of COUNTIF() to Count cells that are blank
You can also count the number of blank cells by using COUNTIF() function. Let’s see how to achieve the same result using COUNTIF() function.
- Select any column, where you want to display a count for a particular range. In this case select N9.
- Go to the function box and type
- Provide the arguments i.e. the range. For N9, assign the range D9:D16,””. The count came out to be 4. The inverted commas, given as arguments tell the COUNTIF() function to look for blank cells.