How to sum with OR conditions on different columns in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11)Add the tests and keep rows where the result is above 0: =SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11).
- Result on the example
- $8,830 in
G3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
Each test gives 1 for a match and 0 otherwise. Adding them gives 2 for a West laptop, 1 for a West monitor or an East laptop, and 0 for everything else. >0 turns any match into one TRUE, so a row that meets both conditions is still counted once.
=SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11)Result8830
B2:B1110 rows × 1 col: "West", "East", "West", "North", …A range: 10 cells in one column.
G1"West"The value in G1.
B2:B11=G1{10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
C2:C1110 rows × 1 col: "Laptop", "Monitor", "Monitor", "Laptop", …A range: 10 cells in one column.
G2"Laptop"The value in G2.
C2:C11=G2{10 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
B2:B11=G1)+(C2:C11=G2{10 rows × 1 col} 2, 0, 1, 1, …The + operator adds.
B2:B11=G1)+(C2:C11=G2)>0{10 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The > operator tests greater than.
D2:D1110 rows × 1 col: $1,840, $420, $395, $1,720, …A range: 10 cells in one column.
B2:B11=G1)+(C2:C11=G2)>0)*D2:D11{10 rows × 1 col} 1840, 0, 395, 1720, …The * operator multiplies.
SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11)8830SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B11=G1)+(C2:C11=G2)>0)*D2:D11{10 rows × 1 col} 1840, 0, 395, 1720, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11). We ran it in Google Sheets mode on the same data and it returns $8,830.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(D2:D11,B2:B11,G1)+SUMIFS(D2:D11,C2:C11,G2)-SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2) | $8,830 | Add both groups, then subtract the rows that are in both. Works in every version without arrays. |
| =SUMIFS(D2:D11,B2:B11,G1)+SUMIFS(D2:D11,C2:C11,G2) | $12,770Different result | Two SUMIFS added without the correction. West laptops are counted twice: 12,770. |
Common mistakes
- Adding two SUMIFS double-counts rows that match both conditions. Either subtract the overlap or use the
>0test. - Leaving out
>0multiplies a double match by 2, which counts it twice as well.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum with OR conditions on different columns
G3, total the sales that are in the region in G1 or are the product in G2.Questions
How do I write the same thing with FILTER?
In Excel 365 or Google Sheets: =SUM(FILTER(D2:D11,(B2:B11=G1)+(C2:C11=G2))). FILTER keeps any row where the sum isn't 0.
How do I add an AND condition as well?
Multiply it in: =SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*(A2:A11>=DATE(2026,2,1))*D2:D11).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.