Spreadsheet Practice

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

Formula (Excel and Google Sheets)

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

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

Result on the example
$1,805 in F3
Works in
Every current version
Google Sheets
Same formula

See it work

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

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.

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

Result1805

  1. C2:C1110 rows × 1 col: $1,840, $420, $395, $1,720, …

    A range: 10 cells in one column.

  2. A2:A1110 rows × 1 col: "West", "East", "West", "North", …

    A range: 10 cells in one column.

  3. F1(empty)

    The value in F1.

  4. F1=""TRUE

    The = operator tests whether the two sides are equal.

  5. IF(F1="","*",F1)"*"

    IF: Returns one value if a test is true, another if false.

    • logical_testF1=""TRUE
    • value_if_true"*"
    • value_if_falseF1(empty)not used
  6. B2:B1110 rows × 1 col: "Laptop", "Monitor", "Monitor", "Laptop", …

    A range: 10 cells in one column.

  7. F2"Monitor"

    The value in F2.

  8. F2=""FALSE

    The = operator tests whether the two sides are equal.

  9. IF(F2="","*",F2)"Monitor"

    IF: Returns one value if a test is true, another if false.

    • logical_testF2=""FALSE
    • value_if_true"*"not used
    • value_if_falseF2"Monitor"
  10. SUMIFS(C2:C11,A2:A11,IF(F1="","*",F1),B2:B11,IF(F2="","*",F2))1805

    SUMIFS: Adds values that meet every condition.

    • sum_rangeC2:C1110 rows × 1 col: $1,840, $420, $395, $1,720, …
    • criteria_range1A2:A1110 rows × 1 col: "West", "East", "West", "North", …
    • criteria1IF(F1="","*",F1)"*"
    • criteria_range2B2:B1110 rows × 1 col: "Laptop", "Monitor", "Monitor", "Laptop", …
    • criteria2IF(F2="","*",F2)"Monitor"

In Google Sheets

The same formula works in Google Sheets: =SUMIFS(C2:C11,A2:A11,IF(F1="","*",F1),B2:B11,IF(F2="","*",F2)). We ran it in Google Sheets mode on the same data and it returns $1,805.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(((A2:A11=F1)+(F1=""))*((B2:B11=F2)+(F2=""))*C2:C11)$1,805Each 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.

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 SUMIFS ignore a criterion when the cell is blank

In F3, total the sales for the region in F1 and the product in F2, treating a blank input as "all".

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.

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.