How to sum with OR criteria in Excel
Excel formula
=SUM(SUMIFS(D2:D11,C2:C11,G1:G2))Give SUMIFS a list of values and add up the results: =SUM(SUMIFS(D2:D11,C2:C11,G1:G2)) totals rows matching either product.
- Result on the example
- $2,200 in
G3 - Works in
- Every current version
- Google Sheets
- Different formula, see below
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
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.
=SUM(SUMIFS(D2:D11,C2:C11,G1:G2))Result2200
D2:D1110 rows × 1 col: $1,840, $420, $395, $1,720, …A range: 10 cells in one column.
C2:C1110 rows × 1 col: "Laptop", "Monitor", "Monitor", "Laptop", …A range: 10 cells in one column.
G1:G22 rows × 1 col: "Monitor", "Dock"A range: 2 cells in one column.
SUMIFS(D2:D11,C2:C11,G1:G2){2 rows × 1 col} 1805, 395SUMIFS: Adds values that meet every condition.- sum_range
D2:D1110 rows × 1 col: $1,840, $420, $395, $1,720, … - criteria_range1
C2:C1110 rows × 1 col: "Laptop", "Monitor", "Monitor", "Laptop", … - criteria1
G1:G22 rows × 1 col: "Monitor", "Dock"
- sum_range
SUM(SUMIFS(D2:D11,C2:C11,G1:G2))2200SUM: Adds numbers and ranges. Skips text and blanks.- number1
SUMIFS(D2:D11,C2:C11,G1:G2){2 rows × 1 col} 1805, 395
- number1
In Google Sheets
Google Sheets needs a different formula: =SUMPRODUCT(SUMIFS(D2:D11,C2:C11,G1:G2)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(D2:D11,C2:C11,G1)+SUMIFS(D2:D11,C2:C11,G2) | $2,200 | One SUMIFS per value, added. Easy to read for two or three values. |
| =SUMPRODUCT(ISNUMBER(MATCH(C2:C11,G1:G2,0))*D2:D11) | $2,200 | 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.
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 criteria
G3, total the sales for the product in G1 or the product in G2.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.
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.