How to Count Blank Cells with Condition in Excel (4 Easy Methods)

We often need to count the blank cells while dealing with a dataset in Excel. The purpose to count those blank cells could be anything. Maybe you want to analyze the data table, you want to synthesize another data table from the existing one by trimming the blank cells, or maybe you want to extract only the solid information by ignoring the blank cells with no information. Whatever the purpose that you possess, follow this article because you are going to learn 3 methods that will help you count blank cells in Excel with the condition.

Download Workbook

You are recommended to download the Excel file and practice along with it.

4 Ways to Count Blank Cells in Excel with Condition

We will be using a sample student mark sheet as our dataset to demonstrate all the methods throughout the article. Where we will try to calculate the number of backlogs each of the students carries.

So, without having any further discussion let’s dive straight into the methods all one by one.

1. Using IFS and COUNTIF Functions

First and foremost, we are choosing the IFS function because it enables us to add multiple criteria while counting the number of blank cells in Excel. In this section, we will count the number of backlogs carried by each of the students from the morning shift. So, let’s see how we can do it step by step.

🔗 Steps:

❶ First of all, select cell G5 ▶ enter the formula below.

📃 Note:  You can use the IF function instead of using the IFS function to solve the problem.

Read more: Count Empty Cells in Excel

2. Utilizing IF and COUNTBLANK Functions

Now, we will try to do an interesting thing with the IF and COUNTBLANK functions. We will show the message “No backlog” instead of showing 0 and for the other number of backlogs, we will simply specify the number. Interesting, right? Then let’s not waste time further but follow the steps below:

🔗 Steps:

❶ First of all, select cell G5 ▶ to store the number of backlogs.

❷ Then type the formula

Read more: How to Count Filled Cells in Excel 

3. Implementing COUNTIFS Function

For one thing, we can apply the COUNTIFS function to count blank cells with condition. So, let’s see it in action.

🔗 Steps:

• First and foremost, enter the expression below into the C17 cell.

Here, the B5:B14 and C5:C14 arrays represent the “Product” and “Sales Quantity”.

4. Applying IF and SUMPRODUCT Functions

Now we will try to count the blank cells i.e. the number of backlogs only when the shift period is given using the IF and SUMPRODUCT functions. If the shift period is missing, we will show “N/A” in the cell. So let’s get into the procedural steps:

🔗 Steps:

❶ First of all, select cell G5 ▶ type in the equation below.

Things to Remember

📌 The IF function facilitates only one condition. If you need more than one then use the IFS function.

📌 Be careful while selecting the range inside formulas.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Conclusion

To sum up, we have introduced 3 methods that you can use to count blank cells with the various conditions in Excel. You are recommended to practice all the methods along with the provided Excel practice workbook. Feel free to ask any questions regarding the methods demonstrated in this article. We will try to respond to any queries asap.

Read More

• Count Cells That Are Not Blank in Excel (6 Useful Methods)
• Excel Fill Blank Cells With Value Above
• How To Delete Blank Cells In Excel And Shift Data Up
• Excel Count Specific Words In Column
• Count Number Of Specific Characters In A Cell
• Excel Count Number Of Characters In A Cell