# How to sum with OR criteria in Excel

Give SUMIFS a list of values and add up the results: `=SUM(SUMIFS(D2:D11,C2:C11,G1:G2))` totals rows matching either product.

**Excel:** `=SUM(SUMIFS(D2:D11,C2:C11,G1:G2))` (Every current version)

**Google Sheets:** `=SUMPRODUCT(SUMIFS(D2:D11,C2:C11,G1:G2))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Date | Region | Product | Sales |  | Product 1 | Monitor |
| 2026-01-05 | West | Laptop | 1840 |  | Product 2 | Dock |
| 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: `=SUM(SUMIFS(D2:D11,C2:C11,G1:G2))`

Result: $2,200

## How it works

With a range as the condition, SUMIFS returns one total per value: Monitor sales and Dock sales. SUM adds the two. Every row has one product, so no row can be counted twice.

In Excel 2019 and older the SUM version needs Ctrl+Shift+Enter; SUMPRODUCT in its place works without it, and is the version to use in Google Sheets.

## Other ways

- `=SUMIFS(D2:D11,C2:C11,G1)+SUMIFS(D2:D11,C2:C11,G2)`: One SUMIFS per value, added. Easy to read for two or three values.
- `=SUMPRODUCT(ISNUMBER(MATCH(C2:C11,G1:G2,0))*D2:D11)`: Counts each row once even if the list repeats a value.

## Common mistakes

- SUMIFS joins its conditions with AND. `=SUMIFS(D2:D11,C2:C11,G1,C2:C11,G2)` asks for rows that are a Monitor and a Dock at the same time, and returns 0.
- If the same value appears twice in the list, the SUM version adds its rows twice. Use the MATCH version for lists that may repeat.

## Questions

**Can I type the values into the formula?**

Yes, as an array constant: `=SUM(SUMIFS(D2:D11,C2:C11,{"Monitor","Dock"}))`. This form works without Ctrl+Shift+Enter in every version.

**How do I combine OR with an AND condition?**

Add the other pair as usual: `=SUM(SUMIFS(D2:D11,B2:B11,"West",C2:C11,G1:G2))` totals Monitor or Dock sales in the West.

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-criteria