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

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

**Excel:** `=COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Order | Region | Status |  |  | Region | East |
| O-01 | East | Open |  |  | Status |  |
| O-02 | West | Shipped |  |  | Orders |  |
| O-03 | East | Shipped |  |  |  |  |
| O-04 | North | Open |  |  |  |  |
| O-05 | East | Closed |  |  |  |  |
| O-06 | West | Open |  |  |  |  |
| O-07 | East | Open |  |  |  |  |
| O-08 | North | Shipped |  |  |  |  |
| O-09 | West | Closed |  |  |  |  |
| O-10 | East | Shipped |  |  |  |  |
| O-11 | North | Closed |  |  |  |  |
| O-12 | West | Open |  |  |  |  |

Formula in G3: `=COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2))`

Result: 5

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

## Other ways

- `=SUMPRODUCT(((B2:B13=G1)+(G1="")>0)*((C2:C13=G2)+(G2="")>0))`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/countifs-ignore-blank-criteria