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 of counting 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 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.

We will be using a sample student mark sheet as our dataset to demonstrate all the methods throughout the article. 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 to Count Blank Cells with Condition in Excel

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.

2. Counting Blank Cells with Condition with 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

3. Applying COUNTIFS Function to Count Blank Cells with Condition in Excel

For one thing, we can apply the COUNTIFS function to count blank cells with a 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. Combining Excel IF and SUMPRODUCT Functions to Count Blank Cells with Condition

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.

Download Workbook

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

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.

<< Go Back to Count Cells | Formula List | Learn Excel