In this chapter, we will learn how to count blank cells in excel using a simple COUNTBLANK Function which also includes custom criteria using COUNTIF and COUNTIFS Function, Follow the video above to learn how to count blank cells in your worksheet or you can follow the tutorial below.
Previously : Count Specific Word Occurs in a Cell in 4 Easy Steps
If you want to use the same sheet we have used in this tutorial or video then you can download using the link below;
COUNTBLANK Function is the most common function to count blank cells in excel. We have the sample data below where we have incurred multiple blank cells as below;
Suppose you have thousands of entries also got hundreds of blank cells with no data, then you can easily filter it with the help of filter option in data ribbon, but if you want to count blank cells occurs in your range then you can do it simply by following steps;
Type 1 – Count Blank Cells Using COUNTBLANK Function
In the first step we will input COUNTBLANK function in cell B2 as follows;
In COUNTBLANK Function, we have only one argument i.e. range;
Therefore, in range, you need to select the range to count blank cells, in our case its A2:A9;
Now close the parenthesis and click ENTER and here we get 2 blank cells in our worksheet;
It’s as simple as that. In one formula we can count blank cells in our worksheet.
This is the easiest way to count blank cells, but if you want to dig deep and want to apply custom criteria or you came across into such situation then we can use different helper function like COUNTIFs function.
Type 2 – Count Blank Cells Using Custom Criteria
Suppose we have the following sample data;
Here we have product column in A and Quantity sold in B, Our problem is we have got some missing items in B5 and B8. Now we want to find all blank cells or values sold on the basis of product.
In this example, we will count blank cells of quantity sold on the basis of product.
Since it involves two criteria, first we need to select the product type and then the quantity sold range, therefore, for two or more criteria we use COUNTIFs function.
In first step, We will input the COUNTIFs function in D4 as follows;
So, for the first criteria, we need to select a Range of Chips i.e. Product and add the chips into the criteria, therefore, we have two options;
- Criteria_range1 – Here we will select Product range i.e. A2:A9
- criteria1 – Since we have text criteria here, therefore, We will quote chips into this i.e. “Chips”
So, for the second criteria, we need to select a Range of Quantity Sold i.e. B2:B9 and add the blank value symbol (“”) into the criteria, therefore, we have two options;
- Criteria_range1 – Here we will select Quantity sold range i.e. B2:B9
- criteria1 – Since we have blank criteria here, therefore, We will quote and unquote it simply i.e. “”
Therefore, our whole formula will be =COUNTIFS(A2:A9, “Chips”, B2:B9, “”)
Now, close the parenthesis and click ENTER, Here we get the answer i.e. 1
Hope you got to know how to count blank cells using the simple COUNTBLANK function and using different custom criteria on the basis of certain values. If you still have questions in mind do let us know using the comment box below.