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.

excel count dataset for blank cells with condition

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.

=IFS(C5="Morning",COUNTIF(D5:F5, ""))
Condition: Count the blank cells only when the shift is “Morning”.
Formula Breakdown

📌 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.

excel count blank cells with condition using IFS and COUNTIF Functions

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

=IF(COUNTBLANK(D5:F5)=0, "No backlog",COUNTBLANK(D5:F5) )
Condition: Count the blank cells. When the count value is 0, show the message “No backlog” instead.
Formula Breakdown

📌 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.

excel count blank cells with condition Utilizing IF and COUNTBLANK Functions

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.

=COUNTIFS(B5:B14,C16, C5:C14, "")

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

Condition: Count the blank cells for the product name “MacBook Air M1”.
Formula Breakdown
  • 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

Using COUNTIFS function


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.

=IF(C5<>"",SUMPRODUCT((C5<>"")*(D5:F5="")),"N/A")
Condition: Count the blank cells only when the left-side cells are not blank.
Formula Breakdown

📌 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.

Using IF and SUMPRODUCT functions


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.

Practice Section


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

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo