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.

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.

Steps:

=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

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.