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, ""))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) )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”.
- 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")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.
<< Go Back to Count Cells | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
