Excel SUMIFS With OR Criteria: Formulas and Examples

Formulas & functions · Updated November 19, 2026

Excel’s SUMIFS function does not provide a built-in OR operator between criteria: to match one condition or another, add separate SUMIFS results, or use an array of criteria. The safest method for many worksheets is to write one SUMIFS for each alternative and add the results.

Why OR Logic Takes an Extra Step

SUMIFS adds values only when all of its supplied criteria are satisfied. That means criteria in different arguments work like AND: a row must meet the first condition and the second condition and any others you include. For example, asking for sales where the product is Widget and the region is East is a straightforward SUMIFS calculation.

OR asks a different question: include a row if it matches at least one of several possible conditions. SUMIFS has no argument where you can type an OR operator between two criteria. Instead, you can run the function separately for each allowed value and combine the subtotals, or supply multiple criteria values as an array and add the returned subtotals.

For the examples below, assume a worksheet has product names in A2:A7, regions in B2:B7, and sales amounts in C2:C7. The six rows are Widget/East/120, Widget/West/80, Gadget/East/50, Widget/North/40, Gadget/West/70, and Widget/East/30. The values are illustrative; the range references are the important part.

Add Separate SUMIFS Results

With product names in A2:A7, regions in B2:B7, and sales in C2:C7, this formula adds Widget sales from East and West:

=SUMIFS(C2:C7,A2:A7,"Widget",B2:B7,"East")+SUMIFS(C2:C7,A2:A7,"Widget",B2:B7,"West")

The first SUMIFS returns 150: it includes the two East Widget rows, worth 120 and 30. The second returns 80 for the West Widget row. Their sum is 230. In practical terms, the formula says “sum sales for Widgets in East, plus sales for Widgets in West.”

This approach is easy to audit because each SUMIFS represents one alternative. It also works well when the alternatives are different kinds of conditions, or when you want to keep the logic obvious to another person reviewing the workbook. For a short list of values, an explicit addition is often the clearest choice.

If the alternatives grow to dozens of values, maintaining a long chain of function calls becomes inconvenient. A list of criteria in worksheet cells, or an array of criteria, can make the formula shorter. Either way, check that the criteria list contains only the alternatives you intend to include.

Use OR for Several Values in One Column

When you want to match one of several values in the same criteria range, an array constant can pass those choices to SUMIFS. With products in A2:A7, regions in B2:B7, and sales in C2:C7, this formula totals sales for Widget rows in either East or West:

=SUM(SUMIFS(C2:C7,A2:A7,"Widget",B2:B7,{"East","West"}))

SUMIFS calculates a subtotal for each region in the array, and the outer SUM combines those subtotals. The result is 230: East Widgets contribute 150 and West Widgets contribute 80. In current Microsoft 365 for Windows, enter the formula normally; the outer SUM is what combines the returned values into one total.

The same pattern can cover alternatives in a single field without another condition. With regions in B2:B7 and sales in C2:C7, this formula adds East and West sales for all products:

=SUM(SUMIFS(C2:C7,B2:B7,{"East","West"}))

It returns 350: East sales total 200, while West sales total 150. The braces create an array constant, with the two quoted text values separated by a comma in this English-language formula. If you prefer a formula that is immediately familiar to most spreadsheet users, two explicit SUMIFS calls give the same result. In versions of Excel other than Microsoft 365, array handling can differ, so explicit addition is a useful compatibility option.

Put the OR Choices in Cells

A criteria list in cells is more convenient when people need to change the allowed values without editing a formula. For example, place East in E2 and West in E3. With sales in C2:C7 and regions in B2:B7, use:

=SUM(SUMIFS(C2:C7,B2:B7,E2:E3))

The formula returns 350 for the example data. SUMIFS evaluates the two criteria values, and SUM adds their subtotals. You can replace the values in E2:E3 later to update the total. This arrangement can also make a report easier to maintain: label the cells clearly, and keep the criteria list near the result or on a separate, well-labeled worksheet.

Use this pattern when the criteria cells represent separate alternatives for the same field. It is not the same as adding another ordinary SUMIFS criterion argument. Separate criteria arguments mean AND; a range of possible criteria passed as one criterion argument, with the subtotals summed, represents OR.

OR Conditions Across Different Columns

OR across different fields needs more care. Suppose the rule is “include a row if the product is Gadget or the region is East.” Adding a subtotal for all Gadgets to a subtotal for all East sales will count a Gadget sold in East twice. In the sample data, that overlapping row is Gadget/East/50.

With product names in A2:A7, regions in B2:B7, and sales in C2:C7, use inclusion-exclusion: total both groups, then subtract the rows that belong to both:

=SUMIFS(C2:C7,A2:A7,"Gadget")+SUMIFS(C2:C7,B2:B7,"East")-SUMIFS(C2:C7,A2:A7,"Gadget",B2:B7,"East")

The Gadget subtotal is 120, and the East subtotal is 200. The Gadget-and-East overlap is 50, so the result is 120 + 200 − 50 = 270. This is a reliable way to count an overlap once when there are two groups. With more complex combinations, the inclusion-and-subtraction logic can become difficult to track, so test it on a small, understandable set of rows.

Another option is SUMPRODUCT, which can test both conditions row by row and count a matching row once. With the same layout, this version returns 270:

=SUMPRODUCT(C2:C7*--(((A2:A7="Gadget")+(B2:B7="East"))>0))

The two comparisons produce true-or-false results. Adding them and checking whether the sum is greater than zero identifies rows that meet at least one condition; the double unary converts that test to numeric values for multiplication by sales. This is useful for a distinct OR rule, but it is less immediately readable than separate SUMIFS calls. Keep the ranges the same size, and test the result against a simple example before relying on a more elaborate expression.

Choose a Method That Fits the Rule

There is no single best OR formula for every worksheet. Choose based on how many alternatives you have, whether the conditions apply to one field or several, and whether the matching groups can overlap.

  • A few choices in one field: Add separate SUMIFS calls. The logic is visible, and it is easy to check each subtotal.
  • A list of choices that may change: Put the values in cells and use SUM with SUMIFS. This keeps the criteria editable without changing the formula.
  • Several choices in one field in Microsoft 365: Consider an array constant inside SUMIFS, wrapped in SUM. This is compact, though explicit additions may be easier for collaborators to recognize.
  • OR across different fields with possible overlap: Use inclusion-exclusion for a small, clear case or a row-by-row method such as SUMPRODUCT. Do not simply add subtotals unless double counting is acceptable.

For multiple OR choices across several fields, write the requirement in plain language first. Identify which rows should qualify, and determine whether a row can satisfy more than one condition. That simple check often reveals whether adding subtotals is safe or whether overlapping records need to be accounted for.

Criteria, Ranges, and Common Mistakes

SUMIFS criteria can be text, numbers, cell references, or criteria expressions. Text criteria such as East are generally not case-sensitive: East and EAST match the same text for SUMIFS. For comparisons such as greater than a threshold, combine the operator with a cell reference rather than treating the comparison as an ordinary text value. Wildcards are also available for text matching: an asterisk represents any number of characters, a question mark represents one character, and a tilde can escape a wildcard when you need to match it literally.

Pay attention to the shape of the ranges. The sum range and each criteria range should have matching dimensions and refer to corresponding rows. If the sum range covers C2:C100 but a criteria range covers B2:B99, the formula may return an error or fail to represent the intended row-by-row test. Check that headers are excluded consistently and that all ranges start and end on the same rows.

Other common causes of surprising results include:

  • Adding overlapping groups: A row can match more than one OR alternative, making a simple sum of subtotals too large.
  • Text stored differently: A number stored as text may not behave like a numeric value. Check the source data if an apparent match is omitted.
  • Hidden spaces: “East” and “East ” are different text strings. Extra spaces imported from another system can prevent an exact match.
  • Wrong logical relationship: Two criteria arguments in one SUMIFS are AND, even if the intended rule was OR.
  • Unintended wildcards: A criterion containing * or ? can match more text values than expected. Use a tilde when the character itself should be matched literally.

When a total looks wrong, calculate each separate SUMIFS subtotal on its own, then compare the matching rows with the source data. For a formula that adds groups, check whether any records occur in more than one group. Testing a few rows manually is often faster than trying to debug the complete expression at once.

Frequently Asked Questions

Can SUMIFS use OR directly?

No. SUMIFS applies all of its supplied criteria together, so those criteria form an AND test. To create OR logic, add separate SUMIFS results, sum results produced from an array or criteria list, or use a row-by-row approach when appropriate.

How do I sum values for either of two text criteria?

For two alternatives in one field, use one SUMIFS for each value and add the results. Alternatively, pass the alternatives as an array of criteria to SUMIFS and wrap the function in SUM. Both methods total the matching groups.

Does SUMIFS count the same row twice with OR criteria?

A single SUMIFS evaluation does not count a row twice, but adding separate subtotals can: the same row may qualify for more than one subtotal. If your OR conditions can overlap, subtract the overlap or use a method that evaluates each row once.

Can I use cell references for the OR choices?

Yes. Put the alternative criteria in cells, pass that range as the criteria argument to SUMIFS, and wrap the result in SUM. This is useful when the choices change regularly and should be edited without modifying the formula.

Why does SUMIFS return zero when a value appears to match?

Check for extra spaces, text-versus-number differences, inconsistent range boundaries, and criteria that express AND when you meant OR. Also confirm that the data falls within the referenced ranges and that wildcard characters are not changing the match.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.