Excel OR in SUMIFS: Add Multiple Criteria Correctly

Formulas & functions · Updated November 19, 2026

Excel’s SUMIFS function does not have a dedicated OR operator between criteria. To total rows matching one condition or another, add separate SUMIFS calculations, or—in Microsoft 365 and Excel 2021 or later—use an array of alternative criteria inside SUMIFS and wrap the result in SUM.

What OR Means in a SUMIFS Formula

SUMIFS totals values that meet criteria in one or more ranges. When you provide criteria for multiple ranges, Excel applies AND logic: a row must meet every condition. For example, a row might need to be in the East region and have the product Widget. If either condition fails, that row is excluded.

OR logic asks for a match to at least one alternative. A common request is “total Widget sales in the East or West.” The row must be a Widget, and its region may be East or West. The product condition is still an AND condition, while the region alternatives form an OR condition.

This distinction matters because putting two criteria in one SUMIFS does not make them alternatives. The function has no built-in OR keyword or operator. Instead, you express the alternatives through separate calculations or an array of criteria.

How SUMIFS Applies Criteria

The basic structure is SUMIFS(sum_range, criteria_range1, criteria1, ...). The first range contains the numbers to add; each following pair identifies a range to test and the condition to apply. All ranges should cover the same rows. A criterion can be a value, a cell reference, or a comparison expression such as a date or a number greater than a threshold.

Suppose a worksheet has Region in column B, Product in column C, and Sales in column D. The sample rows are East / Widget / 120, West / Widget / 80, East / Gadget / 50, and North / Widget / 40. If you ask for East and Widget, only the first row qualifies: the result is 120. If you ask for East or West and Widget, the first two rows qualify, for a total of 200.

Understanding which part of the question is AND and which part is OR is the key step. It also helps prevent a plausible-looking formula from including too many rows or leaving out valid ones.

Most Reliable Method: Add Separate SUMIFS Results

For a short, fixed list of alternatives, write one SUMIFS for each alternative and add the results. This approach is easy to read and works across Excel versions that support SUMIFS. It is often the clearest choice in a shared workbook because another person can see exactly which alternatives are included.

With Region in B2:B100, Product in C2:C100, and Sales in D2:D100, the following totals Widget sales for East plus Widget sales for West:

=SUMIFS($D$2:$D$100,$B$2:$B$100,"East",$C$2:$C$100,"Widget")+SUMIFS($D$2:$D$100,$B$2:$B$100,"West",$C$2:$C$100,"Widget")

On the four-row sample above, the East calculation returns 120 and the West calculation returns 80, so the formula returns 200. The dollar signs keep the ranges fixed if you copy the formula to another cell. For three alternatives, add a third SUMIFS; for a handful of conditions, this remains straightforward.

The trade-off is length. If the alternatives change often or come from a long list, manually adding a calculation for each one is cumbersome and easy to maintain incorrectly.

Compact Method: Use an Array of Criteria

In Microsoft 365 and Excel 2021 or later, an array constant can supply several alternative criteria to one SUMIFS. The outer SUM adds the separate results produced for those criteria. This is a concise option when the OR alternatives apply to the same criteria range.

With Region in B2:B100, Product in C2:C100, and Sales in D2:D100, this formula sums Widget sales from East and West:

=SUM(SUMIFS($D$2:$D$100,$B$2:$B$100,{"East","West"},$C$2:$C$100,"Widget"))

For the sample data, the East and West subtotals are 120 and 80, so the result is 200. The braces create an array of two text criteria; they are not typed as ordinary worksheet cell references. The comma-separated array constant shown uses English-language Excel formula separators. Regional settings can affect formula separators in some installations.

You can also keep alternatives in cells instead of typing them into the formula. With the values East and West in G2:G3, and the same Region, Product, and Sales ranges, use:

=SUM(SUMIFS($D$2:$D$100,$B$2:$B$100,$G$2:$G$3,$C$2:$C$100,"Widget"))

That returns 200 for the sample rows. A cell list is convenient when people need to change the chosen regions without editing a formula. In versions without the relevant dynamic-array behavior, use the separate-SUMIFS method or test the workbook’s target Excel version before relying on this pattern.

When the OR Conditions Are in Different Columns

Some requests ask for rows that match one condition in one column or a different condition in another—for example, “total a row if its region is East or its product is Widget.” This is not the same as East or West in one Region column. Simply adding two subtotals can count a row twice if it meets both conditions.

With Region in B2:B100, Product in C2:C100, and Sales in D2:D100, this formula uses SUMPRODUCT to count a qualifying row once, even if it meets both tests:

=SUMPRODUCT($D$2:$D$100*(($B$2:$B$100="East")+($C$2:$C$100="Widget")>0))

On the four sample rows, the East rows total 170 and the Widget rows total 240. The East Widget row, worth 120, belongs to both groups, so the combined OR total is 170 + 240 − 120 = 290. The formula returns 290 because its OR test treats a row as qualifying once, not once per condition. Keep the ranges the same size, and ensure the sales range contains numeric values suitable for the calculation.

If the intended meaning is “East and Widget, or West and Gadget,” think of it as two complete AND groups joined by OR. Add one SUMIFS for East plus Widget and another for West plus Gadget. That construction is clearer than trying to force unrelated column conditions into one criteria pair.

Criteria Lists, Dates, and Wildcards

OR alternatives can be text, numbers, or dates. For example, you might total orders whose status is Open or Pending, or sales recorded on either of two specific dates. Use the same approach as for regions: add separate SUMIFS calculations, or provide multiple criteria for one criteria range where supported. For date comparisons, use real Excel date values or carefully constructed criteria rather than relying on a date’s display format.

Text criteria normally need to match the values in the worksheet. Extra spaces or inconsistent labels—such as “West” and “West ”—can cause an expected match to be missed. Excel’s standard text matching in these functions is not case-sensitive, so “east” and “East” generally match the same text. Wildcards can represent text patterns: an asterisk matches a sequence of characters, and a question mark matches one character. If a criterion needs to match a literal wildcard character, use a tilde to escape it.

When alternatives are maintained in a worksheet list, check for duplicate entries. For separate calculations, duplicate alternatives can cause the same rows to be summed more than once. A criteria array can also produce repeated subtotals if the criteria list repeats a value. Keep each alternative unique unless repeated counting is specifically intended.

Common Errors and How to Check the Result

The most frequent mistake is using AND when the request calls for OR. Two criteria pairs in a single SUMIFS require both criteria to be true. Another common error is adding subtotals for alternatives in different columns without considering overlap, which can double-count rows.

  • Confirm that the sum range and every criteria range start and end on the same rows.
  • Check spelling, spaces, and data types in the source values and criteria.
  • Test the calculation on a small, manually checked sample before applying it to a large table.
  • For separate subtotals, verify that the groups cannot overlap—or adjust the logic if they can.
  • Use absolute references when copying a formula and review the referenced ranges after filling it.

A useful check is to filter the source table to the intended records and compare their visible sales total with the formula. For a formula using multiple alternatives, check each alternative’s subtotal separately as well. If one subtotal is unexpectedly zero, investigate the criterion value or the underlying data before changing the formula structure.

Which Method Should You Choose?

Choose the method that makes the logic easiest to verify and maintain:

  • Use added SUMIFS formulas for a few fixed alternatives, broad version compatibility, and maximum readability.
  • Use an array of criteria inside SUMIFS for a compact formula when several alternatives apply to the same criteria range and the workbook targets Microsoft 365 or Excel 2021 or later.
  • Use a worksheet criteria list when users need to change alternatives without editing formulas.
  • Use SUMPRODUCT or carefully designed separate totals when OR logic spans different columns; explicitly account for any overlapping matches.

In Excel for Microsoft 365 on Windows, these formulas are entered in a cell or the formula bar as usual. The same formula logic generally applies in Excel for Mac and Excel for the web, though interface details and availability can vary by version. For a workbook shared with older Excel editions, the separate-SUMIFS method is the safer compatibility choice.

Frequently Asked Questions

Can I put OR directly inside SUMIFS?

No. SUMIFS applies AND logic to its criteria pairs. To create OR logic, add separate SUMIFS results, or use an array of alternatives for a criteria range in a compatible Excel version.

Does SUMIFS use AND or OR between criteria?

It uses AND. A row must satisfy every supplied criteria pair to be included. OR behavior has to be expressed through multiple calculations or a criteria array.

Can I use cell references for OR criteria?

Yes. Put the alternatives in worksheet cells and use those cells as criteria. In Microsoft 365 or Excel 2021 and later, wrapping the result of a SUMIFS that refers to a criteria range in SUM can add the alternative subtotals. For broad compatibility, add one SUMIFS per cell instead.

Will adding two SUMIFS formulas double-count rows?

Not when the alternatives are different values in the same single-valued field, such as Region equals East or West. It can double-count when the formulas describe overlapping groups, such as a row that is both in the East region and a Widget product. Check whether the groups overlap before adding them.

Why does my SUMIFS OR formula return zero?

Check that the criteria text matches the source data, the sum and criteria ranges align, and numeric sales are stored as numbers. Also confirm that you used a supported array-criteria pattern for the Excel version in use. A typo or trailing space in a label is a frequent cause of a missed 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.