How to Count Blank Cells in Excel  with Condition (3 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 the Practice Workbook

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


3 Methods 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.

count blank cells using student mark sheet as dataset

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


1. Calculate the Number of Blank Cells with Condition Using the IFS Function in Excel

Condition: Count the blank cells only when the shift is morning.

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 to store the number of backlogs.

Then type the formula

=IFS(C5="Morning",COUNTIF(D5:F5, ""))
within the cell.

After that, press the ENTER button.

Calculate the Number of Blank Cells with Condition Using IFS Function in Excel

Now, drag the Fill Handle icon to the end of column G5.

Boom! You are done with it. Here’s the result:

count blank cells fill handle

  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.

The result will look like this then:

usage of if function to calculate the number of blank cells in excel

Read more: Count Empty Cells in Excel

2. Compute the Number of Blank Cells in Excel with Condition Using the IF and COUNTBLANK Functions

Condition: Count the blank cells. When the count value is 0, show a message “No backlog” instead.

Now, we will try to do an interesting thing. We will show a 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) )
within the cell.

After that, press the ENTER button.

Compute the Number of Blank Cells in Excel with Condition Using IF and COUNTBLANK Function

Now, drag the Fill Handle icon to the end of column G5.

Phew! You are done with it. Here’s the result:

 

  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.

Read more: How to Count Filled Cells in Excel 

3. Count Blank Cells with Condition in Excel Using the SUMPRODUCT Function

Condition: Count the blank cells only when the left-side cells are not blank.

Now we will try to count the blank cells i.e. the number of backlogs only when the shift period is given. 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 to store the number of backlogs.

Then type the formula

=IF(C5<>"",SUMPRODUCT((C5<>"")*(D5:F5="")),"N/A")
within the cell.

After that, press the ENTER button.

Count Blank Cells with Condition in Excel Using the SUMPRODUCT Function

Now, drag the Fill Handle icon to the end of column G5.

If you are done with all the previous steps, you will get the result as follows:

sumproduct function and fill handle

  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.

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.


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

Mrinmoy

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

ExcelDemy
Logo