# How to make SUMIFS ignore a criterion when the cell is blank in Excel

Swap a blank input for the wildcard "*": `=SUMIFS(C2:C11,A2:A11,IF(F1="","*",F1),B2:B11,IF(F2="","*",F2))`.

**Excel:** `=SUMIFS(C2:C11,A2:A11,IF(F1="","*",F1),B2:B11,IF(F2="","*",F2))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Region | Product | Sales |  | Region |  |
| West | Laptop | 1840 |  | Product | Monitor |
| East | Monitor | 420 |  | Total |  |
| West | Monitor | 395 |  |  |  |
| North | Laptop | 1720 |  |  |  |
| West | Dock | 210 |  |  |  |
| East | Laptop | 1955 |  |  |  |
| West | Monitor | 610 |  |  |  |
| South | Monitor | 380 |  |  |  |
| West | Laptop | 2100 |  |  |  |
| East | Dock | 185 |  |  |  |

Formula in F3: `=SUMIFS(C2:C11,A2:A11,IF(F1="","*",F1),B2:B11,IF(F2="","*",F2))`

Result: $1,805

## How it works

The asterisk is a wildcard that matches any text. When F1 is blank, `IF(F1="","*",F1)` hands SUMIFS "*", so every region passes and only the product filter applies. Type a region in F1 and the condition becomes a normal match. This is how a dashboard gets optional filters.

## Other ways

- `=SUMPRODUCT(((A2:A11=F1)+(F1=""))*((B2:B11=F2)+(F2=""))*C2:C11)`: Each factor is 1 when the row matches or the input is blank. Works for numbers and dates too, where "*" doesn't.

## Common mistakes

- "*" matches text only. If the column holds numbers or dates, or some cells are empty, those rows drop out. Use the SUMPRODUCT version for them.
- A blank input without the IF matches only empty cells, so `=SUMIFS(C2:C11,A2:A11,F1)` returns 0 when F1 is blank.

## Questions

**How do I use "All" in a dropdown instead of a blank?**

Test for the word: `IF(F1="All","*",F1)`.

**Does this work in Google Sheets?**

Yes. SUMIFS in Google Sheets supports the same wildcards.

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/sumifs-ignore-blank-criteria