How to make COUNTIFS ignore a criterion when its cell is blank

Q: How do I use an "All" option in a drop-down?

Test for it instead of blank: IF(G1="All","*",G1).

Q: How do I ignore a blank date bound?

Use a bound that includes everything: ">="&IF(G3="",0,G3) for a start date and "<="&IF(G4="",99999,G4) for an end date.

Formula (Excel and Google Sheets)

=COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2))

Replace a blank criterion with the "*" wildcard: =COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2)).

Result on the example: 5 in G3
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.

How it works

An empty criteria cell would make COUNTIFS look for empty statuses and return 0. The IF swaps a blank filter for "*", which matches any text, so that condition stops filtering. With G2 empty, the formula counts all East orders: 5.

Fill in G2 with "Open" and the same formula counts East orders that are open.

=COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2))

Result5

B2:B1312 rows × 1 col: "East", "West", "East", "North", …
A range: 12 cells in one column.
G1"East"
The value in G1.
G1=""FALSE
The = operator tests whether the two sides are equal.
IF(G1="","*",G1)"East"
IF: Returns one value if a test is true, another if false.
- logical_testG1=""FALSE
- value_if_true"*"not used
- value_if_falseG1"East"
C2:C1312 rows × 1 col: "Open", "Shipped", "Shipped", "Open", …
A range: 12 cells in one column.
G2(empty)
The value in G2.
G2=""TRUE
The = operator tests whether the two sides are equal.
IF(G2="","*",G2)"*"
IF: Returns one value if a test is true, another if false.
- logical_testG2=""TRUE
- value_if_true"*"
- value_if_falseG2(empty)not used
COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2))5
COUNTIFS: Counts rows that meet every condition.
- criteria_range1B2:B1312 rows × 1 col: "East", "West", "East", "North", …
- criteria1IF(G1="","*",G1)"East"
- criteria_range2C2:C1312 rows × 1 col: "Open", "Shipped", "Shipped", "Open", …
- criteria2IF(G2="","*",G2)"*"

In Google Sheets

The same formula works in Google Sheets: =COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2)). We ran it in Google Sheets mode on the same data and it returns 5.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT(((B2:B13=G1)+(G1="")>0)*((C2:C13=G2)+(G2="")>0))	5	Each condition is "matches, or the filter is blank". Unlike "*", this also works for numbers and dates.

Common mistakes

"*" only matches text, so rows with an empty or numeric value in that column drop out even when the filter is blank. Use the SUMPRODUCT version for number and date columns.
A filter cell containing a space is not blank. Use TRIM(G1)="" if users type in it.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Make COUNTIFS ignore a criterion when its cell is blank

In G3, count the orders that match the region in G1 and the status in G2, ignoring a filter cell that is left blank.

Questions

How do I use an "All" option in a drop-down?

Test for it instead of blank: IF(G1="All","*",G1).

How do I ignore a blank date bound?

Use a bound that includes everything: ">="&IF(G3="",0,G3) for a start date and "<="&IF(G4="",99999,G4) for an end date.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.