# How to sum with OR conditions on different columns in Excel

Add the tests and keep rows where the result is above 0: `=SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11)`.

**Excel:** `=SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Date | Region | Product | Sales |  | Region | West |
| 2026-01-05 | West | Laptop | 1840 |  | Product | Laptop |
| 2026-01-11 | East | Monitor | 420 |  | Total |  |
| 2026-01-19 | West | Monitor | 395 |  |  |  |
| 2026-02-02 | North | Laptop | 1720 |  |  |  |
| 2026-02-09 | West | Dock | 210 |  |  |  |
| 2026-02-16 | East | Laptop | 1955 |  |  |  |
| 2026-02-23 | West | Monitor | 610 |  |  |  |
| 2026-03-03 | South | Monitor | 380 |  |  |  |
| 2026-03-10 | West | Laptop | 2100 |  |  |  |
| 2026-03-17 | East | Dock | 185 |  |  |  |

Formula in G3: `=SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11)`

Result: $8,830

## 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.

## Other ways

- `=SUMIFS(D2:D11,B2:B11,G1)+SUMIFS(D2:D11,C2:C11,G2)-SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2)`: 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)`: 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 `>0` test.
- Leaving out `>0` multiplies a double match by 2, which counts it twice as well.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-with-or-conditions-on-different-columns