Nazmus Sakib
Sometimes blank cells in a dataset are undesirable. However, we can use blank cells as conditions to perform various operations in Excel. In this article, we will show how to count blank cells with condition in Excel with 3 convenient approaches.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Count Blank Cells with Condition in Excel with These 3 Suitable Approaches
In this article, we will use the following sample dataset to count blank cells with conditions. Here, we will calculate the number of pending payments for each employee with an ID.
1. Using IF Function
In our first approach, we will use IF and COUNTIF functions to show how to count blank cells with condition.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, select cell G6 and type in the following formula, and press Enter:
=IF(C6<>””,COUNTIF(D6:F6,””),”Not Applicable”)
- Next, from the bottom right corner of cell G6, use the Fill Handle icon and double-click.
- Voila! All the cells of the Pending column show the number of pending payments for each employee.
🔨 Formula Breakdown
👉 If cell C6 is not empty, the IF function will return the number of blank cells from cells D6:F6 counted by the COUNTIF function.
👉 If the employees do not have an ID in cell C6, the IF function will show Not Applicable.
Read More: 3 Easy Ways to Count Odd and Even Numbers in Excel
2. Applying IF and SUMPRODUCT Functions
Now we will demonstrate how to count blank cells with condition by applying the IF and SUMPRODUCT functions.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell G6 and Enter the formula below:
=IF(C6=””,”Not Applicable”,SUMPRODUCT((C6<>””)*(D6:F6=””)))
- Later, use the Fill Handle icon just like in the previous method to apply the formula to the whole Pending column.
- Done! You will get the number of pending payments in the right-most column.
🔨 Formula Breakdown
IF(C6=””,”Not Applicable”,SUMPRODUCT((C6<>””)*(D6:F6=””)))
👉 The IF function will return Not Applicable if cell C6 is blank.
👉 Otherwise, the SUMPRODUCT function will return the number of blank cells from cells D6:F6 as pending payments.
Read More: 5 Examples to Count Filled Cells Using VBA in Excel
3. Utilizing IF and COUNTBLANK Functions
In our final approach, we will learn how to utilize the IF and COUNTBLANK functions to count blank cells with condition.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell G6 and Enter the following formula:
=IF(COUNTBLANK(D6:F6)=0,”No”,COUNTBLANK(D6:F6))
- Afterward, use the Fill Handle icon and double-click.
🔨 Formula Breakdown
IF(COUNTBLANK(D6:F6)=0,”No”,COUNTBLANK(D6:F6))
👉 If the COUNTBLANK function finds no blank cells between cells D6:F6, the IF function will return No.
👉 Else, the IF function will return the number of blank cells between cells B6:F6 with the help of the COUNTBLANK function.
How to Count Non-Blank Cells with Condition in Excel
Here, we will learn how to count non-blank cells with condition in Excel by employing the COUNTIF function. Let’s start!
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell G6, and Enter the following formula:
=COUNTIF(D6:F6,”>350″)
- Later, use the Fill Handle icon to apply the formula to the column.
- You are all set. We have calculated how many payments were greater than $350 in the G column.
📝 Takeaways from This Article
📌 Firstly, we showed how to use the IF function to count blank cells with condition.
📌 Secondly, we demonstrated how to employ IF and SUMPRODUCT functions to count blank cells with conditions.
📌 Next, we learned how to count blank cells with conditions by utilizing IF and COUNTBLANK functions.
📌 Finally, we illustrated how to count non-blank cells with condition employing the COUNTIF function.
Conclusion
That concludes the discussion for today. These are some convenient methods to count blank cells with condition in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
