Sum with OR Conditions

Excel Formulas › Sum

All versionsSUMIF

SUMIFS combines conditions with AND. To sum where a field is “A” or “B,” add SUMIFs together — or use SUMPRODUCT with added condition arrays.

Quick formula: sum amounts where region is East or West:
=SUM(SUMIF(region, {"East","West"}, amount))
Passing an array of criteria makes SUMIF return one total per value; SUM adds them — an OR sum.

Functions used (tap for the full reference guide):

SUMIFSUMPRODUCT

The example

East and West totals combined.

AB
1RegionSales
2East100
3West150
4South80
5East or West250

The formula

Array criteria, then SUM:

=SUM(SUMIF(region, {"East","West"}, amount)) // OR across two values

How it works

Add the matches together:

  1. SUMIF(region, {"East","West"}, amount) returns an array of two totals.
  2. Wrapping in SUM adds them — the OR sum.
  3. For OR with SUMPRODUCT: =SUMPRODUCT(((region="East")+(region="West"))*amount) — add the condition arrays.
  4. Watch double-counting: this works because a row can’t be both East and West.

Overlapping OR conditions double-count. If a row could satisfy two of your OR conditions, the additive approach counts it twice. For mutually-exclusive values (like region) it’s exact; otherwise use a SUMPRODUCT with SIGN or a deduplicating approach.

Try it: interactive demo

Live demo

Lines “region,sales”; sum East or West.

East+West:

Variations

SUMPRODUCT OR

Add the arrays:

=SUMPRODUCT(((r="East")+(r="West"))*amt)

OR within AND

Region East/West AND year 2026:

=SUM(SUMIFS(amt, region, {"East","West"}, yr, 2026))

Contains either word

Wildcards:

=SUM(SUMIF(r, {"*north*","*east*"}, amt))

Pitfalls & errors

Mutually exclusive only (additive). Adding matches double-counts rows that satisfy two conditions. Fine for distinct values like region.

Array braces. Use {"East","West"} with commas (or your locale’s separator).

Don’t nest OR inside SUMIFS arguments. SUMIFS is AND-only; build OR by summing.

Practice workbook

📊
Download the free Sum with OR Conditions practice workbook
An OR-sum sheet with the SUMPRODUCT, OR-within-AND, and wildcard variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum with OR conditions in Excel?
Add the matches: =SUM(SUMIF(range, {"East","West"}, amounts)). The array criteria return one total per value and SUM combines them.
What's the SUMPRODUCT version?
Add the condition arrays: =SUMPRODUCT(((region="East")+(region="West"))*amount).
Why might an OR sum double-count?
If a row can satisfy two OR conditions at once, the additive approach counts it twice. It's exact only for mutually exclusive values.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SUMIFS multiple criteria · SUMPRODUCT formula · Sum if contains

Function references: SUMIF · SUMPRODUCT