# How to Count Blank Cells with Condition in Excel: 4 Easy Methods

## Method 1 – Using IFS and COUNTIF Functions to Count Blank Cells with Condition in Excel

Steps:

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 the IFS function to solve the problem.

## Method 2 – Counting Blank Cells with Condition with IF and COUNTBLANK Functions

Steps:

Select cell G5 ▶ to store the number of backlogs.

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

## Method 3 – Applying COUNTIFS Function to Count Blank Cells with Condition in Excel

Steps:

• 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. The B5:B14 cells represent the criteria_range1 argument, the C16 cell indicates the criteria1 In a similar fashion, the C5:C14 cells represent the criteria_range2 argument, the “” (blank) indicates the criteria2 argument.
• Output → 2

## Method 4. Combining Excel IF and SUMPRODUCT Functions to Count Blank Cells with Condition

Steps:

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.

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

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

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

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF