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.
=IFS(C5="Morning",COUNTIF(D5:F5, ""))📌 Syntax: =IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)
- COUNTIF(D5:F5, “”) ▶ searches for blank cells within the range D5:F5.
- C5=”Morning” ▶ cross-checks whether the shift is Morning or Evening.
- =IFS(C5=”Morning”, COUNTIF(D5:F5, “”)) ▶ counts the number of blank cells i.e. the number of backlogs in each row having the morning shift period.
📃 Note: You can use the IF function instead of using the IFS function to solve the problem.
Read more: How to Count Non Blank Cells with Condition in Excel
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
=IF(COUNTBLANK(D5:F5)=0, "No backlog",COUNTBLANK(D5:F5) )📌 Syntax: IF(logical_test, value_if_true, [value_if_false])
- COUNTBLANK(D5:F5) ▶ counts the blank cells within the range D5:F5.
- COUNTBLANK(D5:F5)=0, “No backlog” ▶ shows the message “No backlog” where the count value is zero.
- =IF(COUNTBLANK(D5:F5)=0, “No backlog”,COUNTBLANK(D5:F5) ) ▶ shows “No backlog” when the count value is 0. Otherwise, it shows the specific count value i.e. the total number of backlogs.
Read more: How to Count Empty Cells in Excel
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.
=COUNTIFS(B5:B14,C16, C5:C14, "")
Here, the B5:B14 and C5:C14 arrays represent the “Product” and “Sales Quantity”.
- COUNTIFS(B5:B14,C16, C5:C14, “”) → counts the number of cells specified by a given set of conditions and criteria. Here, the B5:B14 cells represent the criteria_range1 argument, whereas the C16 cell indicates the criteria1 In a similar fashion, the C5:C14 cells represent the criteria_range2 argument, whereas the “” (blank) indicates the criteria2 argument.
- Output → 2
Read More: How to Count Cells That are Not Blank in Excel
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.
=IF(C5<>"",SUMPRODUCT((C5<>"")*(D5:F5="")),"N/A")📌 Syntax: =SUMPRODUCT(array1, [array2], [array3], …)
- (C5<>””) ▶ checks whether cell C5 is empty.
- SUMPRODUCT((C5<>””)*(D5:F5=””)) ▶ counts the blank cells only when the shift period info is given.
- =IF(C5<>””,SUMPRODUCT((C5<>””)*(D5:F5=””)),”N/A”) ▶ counts the blank cells only when the shift period info is given. Otherwise, it shows “N/A” within the cells.
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.
