SUMIFS: Ignore Blank Criteria and Show All When Empty

To make SUMIFS ignore blank criteria, replace each empty filter cell with the * wildcard using IF. Then an empty cell means "any value", and your report shows the full total instead of 0. This guide is for anyone building a small report where people type or pick filters, and some filters are often left empty.

Why SUMIFS returns 0 when a criteria cell is blank#

SUMIFS doesn't skip a condition just because its criteria cell is empty. It still tries to match every row against the empty value. Microsoft's COUNTIFS page notes that an empty criteria cell is treated as 0, and SUMIFS uses the same criteria rules. Rows with real text in that column don't match, so they drop out.

All the examples use this sales list in A1:C8, with filter cells for Region in F2 and Channel in F3:

F2 holds North and F3 is empty. The obvious formula is:

=SUMIFS(C2:C8,A2:A8,F2,B2:B8,F3)

It returns 0, not the North total. Every row has a channel, so no row matches the empty F3. If SUMIFS with several conditions is new to you, start with COUNTIFS and SUMIFS with multiple conditions.

How to ignore a blank criteria cell with IF and *#

The fix is to give SUMIFS a wildcard when the filter cell is empty. The asterisk * means "any text", so a condition of * lets every text value through.

=SUMIFS(C2:C8,A2:A8,IF(F2="","*",F2),B2:B8,IF(F3="","*",F3))

Here is what the formula returns for each combination of filters:

Each filter now works on its own. Test every combination like this before you share a report, because a filter that silently returns 0 is easy to miss. The guide to wildcards in SUMIF and COUNTIF criteria explains * and ? in more detail, and the IF function guide covers the IF part.

How to add an "All" option to a drop-down filter#

If the filter is a drop-down list, add All as the first item and test for it instead of an empty cell:

=SUMIFS(C2:C8,A2:A8,IF(F2="All","*",F2))

When the user picks All, SUMIFS gets * and returns 5350. When they pick North, it returns 2250. An explicit All is clearer than an empty cell, because nobody wonders whether the filter was forgotten. To cover both, test for either: IF(OR(F2="",F2="All"),"*",F2).

What if the criteria column holds numbers?#

The * wildcard matches text only, so it skips numbers. If the column holds numbers, such as store numbers or years, use "<>" instead. It means "not empty" and matches any number or text:

=SUMIFS(C2:C8,D2:D8,IF(F4="","<>",F4))

Here D2:D8 would be a store-number column and F4 the store filter. When F4 is empty, every row with a store number counts.

How to include rows where the data itself is blank#

Both * and "<>" leave out rows where that column is empty in the data. Usually that is fine. If those rows must count too, switch to SUMPRODUCT, which can treat an empty filter as "always true":

=SUMPRODUCT(C2:C8*((A2:A8=F2)+(F2="")>0)*((B2:B8=F3)+(F3="")>0))

Each bracket is TRUE when the row matches the filter or the filter is empty. With North in F2 and F3 empty, the result is 2250, the same as before, but rows with no channel would now count as well. The article on SUMIF with blank and non-blank cells covers summing the blank rows themselves.

How to show which filters are active#

When an empty filter means "all", say so on the report, so nobody misreads the total. Build a label with the same IF tests:

="Showing: "&IF(F2="","all regions",F2)&", "&IF(F3="","all channels",F3)

With North in F2 and F3 empty, the label reads Showing: North, all channels. Put it right above the total.

Use the same trick in COUNTIFS and AVERAGEIFS#

Every function in the IFS family uses the same criteria rules, so the same IF wrapper works:

=COUNTIFS(A2:A8,IF(F2="","*",F2),B2:B8,IF(F3="","*",F3))

With North in F2 and F3 empty, this counts 3 orders. Swap COUNTIFS for AVERAGEIFS, MAXIFS or MINIFS and the filters behave the same way. For a full category summary with one total per row, see how to sum by category in Excel.

Common mistakes#

These are the errors people make most when building optional filters:

1. Returning "" instead of "*". IF(F2="","",F2) still matches only empty cells, so the total stays at 0.
2. Putting the cell in quotes. "F2" means the letters F and 2, not the value in F2.
3. Using * on a number column. The wildcard ignores numbers. Use "<>" for those columns.
4. Partial matches by accident. "*"&F2 is a popular shortcut, but with F2 = North it also matches any text that ends in North, such as Far North. The IF version matches exactly.
5. Trying to combine filters with OR. These filters are AND logic: every condition must be true. For North or South, add two SUMIFS, as shown in SUMIFS with OR logic.

The SUMIFS function guide lists every argument and the limits of each criteria type.

Practise it#

Optional filters are a favourite in intermediate Excel tests, because they check that you understand criteria rather than just copy formulas. Practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula and it checks it instantly. Then practise wildcards and cell-based criteria, where * and criteria that point at a cell are the whole lesson.