How to / Sums and averages
How to use SUMIF with OR (this or that) in Excel
Add one SUMIF for each value: =SUMIF(A2:A5, "North", B2:B5)+SUMIF(A2:A5, "South", B2:B5).
Sales for North or South
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | ||
| 2 | North | 1200 | 2300 | |
| 3 | South | 800 | ||
| 4 | West | 400 | ||
| 5 | North | 300 |
In D2: =SUMIF(A2:A5, "North", B2:B5)+SUMIF(A2:A5, "South", B2:B5) → 2300
1,200 + 300 for North, and 800 for South.
How it works
- SUMIF takes one condition. SUMIFS takes several, but a row must meet all of them, which is AND.
- For OR, work out each total on its own and add them.
- A region cannot be both North and South, so no row is counted twice.
Variations
Shorter, with a list of values
A B C D 1 Region Sales 2 North 1200 2300 3 South 800 4 West 400 5 North 300 In D2:
=SUM(SUMIF(A2:A5, {"North","South"}, B2:B5))→ 2300The list in braces makes SUMIF return one total per value, and SUM adds them.
Count the rows instead
A B C D 1 Region Sales 2 North 1200 3 3 South 800 4 West 400 5 North 300 In D2:
=COUNTIF(A2:A5, "North")+COUNTIF(A2:A5, "West")→ 3OR across two different columns
A B C D 1 Region Sales 2 North 1200 2300 3 South 800 4 West 400 5 North 300 In D2:
=SUMPRODUCT(((A2:A5="North")+(B2:B5>=800)>0)*B2:B5)→ 2300North, or 800 and over. A row that meets both tests must count once, so the tests are added and compared with 0.
Watch out for
- Counting a row twice. Adding two SUMIFs is only right when no row can meet both conditions. When the conditions are on different columns, use the SUMPRODUCT version.
- Typing OR between the values.
=SUMIF(A2:A5, "North" OR "South", B2:B5)is not a formula Excel can read. OR is a function, and it does not work inside a condition.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- SUMIF: Adds the cells that meet one condition.
- SUMIFS: Adds the cells that meet every one of several conditions.
- SUMPRODUCT: Multiplies matching items in arrays, then adds the results.
- COUNTIF: Counts the cells that meet a condition.
- OR: TRUE if at least one condition is TRUE.