The formula
ExcelExcel
=SUM(SUMIFS(C2:C7,A2:A7,{"East","West"}))In E2 → 680
Excel & Google SheetsExcelGoogle Sheets
=SUMIFS(C2:C7,A2:A7,"East")+SUMIFS(C2:C7,A2:A7,"West")In E3 → 680
OR in one column + AND in anotherExcel
=SUM(SUMIFS(C2:C7,A2:A7,{"East","West"},B2:B7,"Pens"))In E4 → 215
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | ||
| 2 | East | Pens | 120 | 680 | |
| 3 | West | Pens | 95 | 680 | |
| 4 | East | Paper | 180 | 215 | |
| 5 | North | Pens | 60 | ||
| 6 | West | Paper | 210 | ||
| 7 | East | Ink | 75 |
How it works
- {"East","West"}
- An array constant: SUMIFS runs once for each value and returns two totals.
- SUM(…)
- Adds those totals.
- B2:B7,"Pens"
- Extra criteria still apply to every OR value (AND).
Common errors and fixes
Only the first value is counted?
In Google Sheets, use the "+" version or wrap in ARRAYFORMULA.
Double counting?
Don't use OR across two different columns this way; a row matching both gets counted twice. Use SUMPRODUCT instead.
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.