SUMIFS With OR Logic in Excel

SUMIFS adds numbers that meet every condition you give it, which is AND logic. To sum with an OR condition, such as "North or East", you add separate sums together, use an array constant, or use SUMPRODUCT. This guide is for anyone who already uses SUMIFS and keeps hitting the "or" case, and it shows each method on the same sales table with the results.

Why SUMIFS can't do OR on its own#

SUMIFS only adds a row when all its conditions are TRUE. That's what you want for "North and Laptop", but not for "North or East".

Here is the sales data used throughout this guide:

The tempting formula is wrong:

=SUMIFS(E2:E9,A2:A9,"North",A2:A9,"East")

It returns 0, because it asks for rows where the region is North and East at the same time. No row can be both. If AND and OR are still fuzzy, the guide to AND, OR and NOT in Excel explains the difference with simple tests.

Method 1: Add two SUMIFS together#

The simplest way to get OR is one SUMIFS per option, joined with +.

=SUMIFS(E2:E9,A2:A9,"North")+SUMIFS(E2:E9,A2:A9,"East")

North is 2,700 + 1,000 = 3,700, and East is 1,250 + 1,800 + 400 = 3,450. The result is 7,150.

This works in every version of Excel and is easy to read in a test. Its only downside is length: five options means five SUMIFS. For a reminder of the argument order (the sum range comes first in SUMIFS), see the COUNTIFS and SUMIFS guide or the SUMIFS function reference.

Method 2: Use an array constant inside SUM#

An array constant is a list of values in curly braces, such as {"North","East"}. Put it in the criteria slot and SUMIFS calculates one total per item. SUM then adds them.

=SUM(SUMIFS(E2:E9,A2:A9,{"North","East"}))

SUMIFS returns two totals, 3,700 and 3,450, and SUM gives 7,150. You type the braces yourself; this is not a Ctrl+Shift+Enter formula, and it works in all current Excel versions.

You can mix OR and AND. North or East, but only Laptops:

=SUM(SUMIFS(E2:E9,A2:A9,{"North","East"},C2:C9,"Laptop"))

The result is 4,500 (North Laptop 2,700 + East Laptop 1,800).

To use a list of regions typed in cells, say H2:H3, instead of typing them into the formula, use =SUMPRODUCT(SUMIFS(E2:E9,A2:A9,H2:H3)). SUMPRODUCT handles the range without special entry in older versions; in Microsoft 365, =SUM(SUMIFS(E2:E9,A2:A9,H2:H3)) also works.

Method 3: SUMPRODUCT for OR across different columns#

When the OR uses two different columns, adding SUMIFS counts some rows twice. Take "Region is North OR Product is Laptop":

=SUMIFS(E2:E9,A2:A9,"North")+SUMIFS(E2:E9,C2:C9,"Laptop")

This returns 10,900, which is too high. Row 2 (North and Laptop, 2,700) is counted in both SUMIFS. The true total is 8,200.

SUMPRODUCT fixes this by testing each row once:

=SUMPRODUCT(((A2:A9="North")+(C2:C9="Laptop")>0)*E2:E9)

How it works:

1. (A2:A9="North")+(C2:C9="Laptop") adds two TRUE/FALSE tests, giving 0, 1 or 2 for each row.
2. >0 turns that into TRUE when at least one test passed, so a row that passes both still counts once.
3. Multiplying by E2:E9 keeps the revenue for those rows, and SUMPRODUCT adds it up.

The result is 8,200. If you prefer SUMIFS, subtract the overlap: =SUMIFS(E2:E9,A2:A9,"North")+SUMIFS(E2:E9,C2:C9,"Laptop")-SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop") also gives 8,200. Our SUMPRODUCT guide explains how multiplying tests works.

Which SUMIFS OR method should you use?#

OR also works with numbers, for example orders that are very small or very large. Total the revenue where Units are under 2 or over 5:

=SUMIFS(E2:E9,D2:D9,"<2")+SUMIFS(E2:E9,D2:D9,">5")

The first SUMIFS picks up row 6 (900), the second picks up rows 8 and 9 (1,500 + 400), and the result is 2,800. No row can be both under 2 and over 5, so there's no double counting. For a range in the middle, such as 2 to 5 units, you need AND instead: =SUMIFS(E2:E9,D2:D9,">=2",D2:D9,"<=5").

For "everything except South and West", AND works naturally: =SUMIFS(E2:E9,A2:A9,"<>South",A2:A9,"<>West") returns 7,150, the same as North plus East here.

The same trick for COUNTIFS and AVERAGEIFS#

Every method above works for counting. To count North or East orders:

=SUM(COUNTIFS(A2:A9,{"North","East"}))

The result is 5. For averages, don't average the two averages, because the groups have different sizes. Divide the OR sum by the OR count instead: =SUM(SUMIFS(E2:E9,A2:A9,{"North","East"}))/SUM(COUNTIFS(A2:A9,{"North","East"})) returns 1,430. For counting filled cells with conditions, see count non-blank cells with a condition, and for counting distinct names see how to count unique values.

Common mistakes with SUMIFS and OR#

• Two criteria on the same column in one SUMIFS, which returns 0.
• Adding SUMIFS across different columns and double-counting the overlap.
• Putting the sum range last. That's SUMIF's order; SUMIFS wants it first.
• Typing {"North";"East"} and {"Laptop","Monitor"} together without understanding them. Two array constants in one SUMIFS make a grid of combinations (a semicolon makes a vertical list, a comma a horizontal one). It works, giving 6,750 for North/East × Laptop/Monitor here, but it is easy to get wrong in a test.

These OR totals come up in HR reports too, such as headcount cost for two departments; see the Excel skills for HR jobs.

Practise it#

Try the live exercise on this page to total North or East revenue. Then practise COUNTIFS and SUMIFS in a live spreadsheet to get the argument order automatic, and use the AND, OR and NOT lesson to see how Excel combines TRUE and FALSE tests.