English

SUMIFS with Multiple Criteria in the Same Column (OR Logic) in Excel & Google Sheets

SUMIFS returns 0 when you give one column two criteria. Sum North OR West with an array constant, a criteria list, FILTER or SUMPRODUCT, without double counting.

Excel How-to By Oscar 7 min read Updated
On this page

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

  • Excel: the SUMPRODUCT + SUMIFS pattern returned 570 in the live Mac Excel check.
  • Excel for Microsoft 365, 2021 and 2024: you can also use SUM instead of SUMPRODUCT, and the FILTER method below.
  • Google Sheets: use ARRAYFORMULA with SUMIF (singular), MAP with scalar SUMIFS, FILTER without an Excel-style if_empty argument, or QUERY. Wrapping an array-criterion SUMIFS in SUMPRODUCT or ARRAYFORMULA did not fix it.

Example data

Type this into A1:C9:

ABC
1RegionProductAmount
2NorthWidget120
3SouthWidget80
4WestGadget200
5NorthGadget150
6EastWidget90
7WestWidget60
8SouthGadget110
9NorthWidget40

Region totals: North 310, West 260, South 190, East 90. So North or West = 570.

Why SUMIFS returns 0 here

Microsoft’s and Google’s SUMIFS documentation both describe the criteria as conditions that must all be met. Google’s page puts it as: “To calculate a sum, meet all criteria in a row.” Two criteria on the same column that can’t both be true at once leave no rows, so the sum is 0.

You don’t need a different function. You need SUMIFS to run once per value and then add the results.

Method 1: Array constant inside SUMIFS (Excel)

=SUMPRODUCT(SUMIFS(C2:C9, A2:A9, {"North","West"}))

How it works: because the criterion is a list of two values, SUMIFS returns two results, {310, 260}. SUMPRODUCT adds them: 570.

In Excel 365 you can write SUM instead:

=SUM(SUMIFS(C2:C9, A2:A9, {"North","West"}))

Google Sheets: use SUMIF or MAP instead

In live Sheets (locale zh_CN), SUMPRODUCT(SUMIFS(...,{"North","West"})) returned 310, not 570. SUM(SUMIFS(...)) and ARRAYFORMULA(SUM(SUMIFS(...))) also returned only 310. Use one of these verified alternatives:

=ARRAYFORMULA(SUM(SUMIF(A2:A9,{"North","West"},C2:C9)))
=SUM(MAP({"North","West"},LAMBDA(region,SUMIFS(C2:C9,A2:A9,region))))

Both returned 570. For a criterion list in F2:F3, use =SUM(MAP(F2:F3,LAMBDA(region,SUMIFS(C2:C9,A2:A9,region)))) (570). With an additional Widget condition, use =SUM(MAP({"North","West"},LAMBDA(region,SUMIFS(C2:C9,A2:A9,region,B2:B9,"Widget")))) (220). Do not copy the Excel two-array SUMIFS pattern into Sheets: its live result was only 160. To include both products, use the row-wise FILTER method with an explicit OR test on each column.

Add more values by extending the list: {"North","West","East"} returns 660 in the Excel pattern or the Sheets SUMIF/MAP alternatives above.

Separator note: in the braces, a comma means “next column”. Google’s arrays help says that in locales that use a comma as the decimal separator, you use a backslash instead: {"North"\"West"}. Excel in those locales uses its own list separator. If the formula is rejected, check your locale.

Method 2: Criteria from a list of cells (Excel)

Hard-coding values in braces gets awkward when the list changes. Put the values in cells, for example F2 North and F3 West:

=SUMPRODUCT(SUMIFS(C2:C9, A2:A9, F2:F3))

Result: 570. Add a value in F4 and extend the range to F2:F4, or use a longer range from the start (empty cells add 0).

This is the best version for dashboards: point F2:F3 at a filter panel, a drop-down, or a list from UNIQUE.

Method 3: OR plus AND and two-list combinations (Excel)

Add more range/criterion pairs as usual. They’re still combined with AND. North or West, Widget only:

=SUMPRODUCT(SUMIFS(C2:C9, A2:A9, {"North","West"}, B2:B9, "Widget"))

Result: 220 (120 + 60 + 40).

OR on two columns at once (North or West, Widget or Gadget). Write one list across (commas) and the other down (semicolons), so SUMIFS evaluates every combination:

=SUMPRODUCT(SUMIFS(C2:C9, A2:A9, {"North";"West"}, B2:B9, {"Widget","Gadget"}))

Result: 570, the four combinations added. If you write both lists with commas, the values are paired up position by position (North with Widget, West with Gadget) instead of crossed. You get 360 instead of 570, and no error to warn you.

Method 4: FILTER (Excel 365 and Google Sheets)

FILTER uses + for OR. Microsoft’s FILTER documentation shows the same operator for “Apples OR East region” examples:

=SUM(FILTER(C2:C9, (A2:A9="North")+(A2:A9="West"), 0))

Result: 570 in Excel. Its third argument returns 0 instead of an error when nothing matches. This version is easy to read, and you can add conditions on any column. Use * for AND: ((A2:A9="North")+(A2:A9="West"))*(B2:B9="Widget") returns 220.

Google Sheets FILTER: omit the third argument. In Sheets, additional arguments are conditions, not an if_empty value; adding 0 caused #N/A with mismatched range sizes.

=SUM(FILTER(C2:C9,(A2:A9="North")+(A2:A9="West")))

This returned 570. Wrap the whole expression in IFNA(…,0) if no-match output should be zero. For the OR region plus OR product combination (570), use:

=SUM(FILTER(C2:C9,((A2:A9="North")+(A2:A9="West"))*((B2:B9="Widget")+(B2:B9="Gadget"))))

In Google Sheets, QUERY also returned 570:

=QUERY(A1:C9, "select sum(C) where A = 'North' or A = 'West' label sum(C) ''", 1)

If that shows a parse error, see Google Sheets QUERY “Unable to Parse Query String” Error: Causes and Fixes.

The double-counting trap (and how to avoid it)

The Excel Method 1 pattern adds one SUMIFS per criterion. That’s fine as long as no row can match two criteria. When criteria overlap, rows get counted twice.

Overlapping wildcards (Excel SUMIFS example). “Starts with N” or “ends with th”:

=SUMPRODUCT(SUMIFS(C2:C9, A2:A9, {"N*","*th"}))

That returns 810, but the right answer is 500 (North 310 + South 190). North matches both patterns, so its 310 is added twice.

OR across different columns. “Region is North or West, or Product is Gadget”. Adding two SUMIFS returns 1030, but the right answer is 680, because West-Gadget and North-Gadget rows match both conditions.

The fix for both: test each row once, then sum the rows where at least one test is true. Adding TRUE/FALSE tests gives a count of matches per row, and >0 turns it back into “yes or no”:

=SUMPRODUCT(C2:C9*(((A2:A9="North")+(A2:A9="West")+(B2:B9="Gadget"))>0))

Result: 680.

=SUMPRODUCT(C2:C9*(((LEFT(A2:A9,1)="N")+(RIGHT(A2:A9,2)="th"))>0))

Result: 500. (SUMPRODUCT doesn’t understand * wildcards, so the tests use LEFT and RIGHT.)

For a cell-based list without any risk of double counting, use MATCH:

=SUMPRODUCT(C2:C9*ISNUMBER(MATCH(A2:A9, F2:F3, 0)))

This still returns 570 if the list has a repeat (F2:F4 = North, West, West). The Excel SUMIFS version (and a scalar-per-criterion MAP version in Sheets) would return 830 because it counts West twice.

Count with OR (rows that are North or West: 5):

=SUMPRODUCT(COUNTIFS(A2:A9, {"North","West"}))

Everything except several values (not North, not West: 280). This one really is AND logic, so a plain SUMIFS works:

=SUMIFS(C2:C9, A2:A9, "<>North", A2:A9, "<>West")

Text that looks the same but doesn’t match. If a total is lower than expected, a region may have a trailing space ("West "). SUMIFS treats it as a different value. See TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160).

Common mistakes

  1. Repeating the same range with a second criterion. SUMIFS(C:C, A:A, "North", A:A, "West") is AND, so it returns 0. Use an array constant or a list.
  2. Assuming SUMIFS expands array criteria in Sheets. SUMPRODUCT and ARRAYFORMULA did not make it expand in the live test. Use SUMIF + ARRAYFORMULA or MAP with one scalar criterion per call.
  3. Overlapping criteria. Wildcards like "N*" and "*th", or OR conditions across columns, count shared rows twice. Use the >0 SUMPRODUCT pattern.
  4. Mixing up commas and semicolons in Excel two-list formulas. One list across, one down. Sheets requires a different approach, as shown above.
  5. Ranges of different sizes. Microsoft notes that each criteria range must have the same number of rows and columns as the sum range.

FAQ

Can SUMIFS use OR logic?

Not by itself. SUMIFS combines criteria with AND. In Excel, sum an array of criteria with SUMPRODUCT. In Sheets, call SUMIFS once per criterion using MAP, or use the SUMIF alternative.

How do I use SUMIFS with multiple criteria in one column?

In Excel, use =SUMPRODUCT(SUMIFS(sum_range, criteria_range, {“Value1”,“Value2”})). In Sheets, use =SUM(MAP({“Value1”,“Value2”},LAMBDA(item,SUMIFS(sum_range,criteria_range,item)))).

Why does my SUMIFS with two criteria on the same column return 0?

Because both criteria have to be true in the same row, and a cell can’t equal two different values at once. Use one of the OR methods in this article.

Does this work in Google Sheets?

The OR logic works, but the array-criterion SUMIFS formula does not work unchanged. Use the tested Sheets SUMIF, MAP, FILTER or QUERY versions above. Locale-specific array separators still apply.

How do I avoid double counting with OR criteria?

Test each row once and sum rows where at least one test is true: =SUMPRODUCT(C2:C9*((test1)+(test2)>0)). This counts each row only once, even if it matches several conditions.

Sources

Oscar

Spreadsheet formulas and troubleshooting. See each article for its actual verification environment and pending author checks.

Tested in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026.

#sumifs#or-logic#sumproduct#array-constants#countifs#google-sheets
Next in Lookup & Conditional Sums COUNTIF vs COUNTIFS vs SUMPRODUCT: When to Use Each (With Examples)