How to sum with multiple criteria in Excel (SUMIFS)
Formula (Excel and Google Sheets)
=SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2)Use SUMIFS with the sum range first, then one range-and-condition pair per criterion: =SUMIFS(sum_range, range1, crit1, range2, crit2).
- Result on the example
- $1,005 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
SUMIFS adds a row only when every condition is true: here the region must equal G1 and the product must equal G2. Unlike SUMIF, the range to add comes first, followed by as many range-and-condition pairs as you need.
=SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2)Result1005
D2:D1110 rows × 1 col: $1,840, $420, $395, $1,720, …A range: 10 cells in one column.
B2:B1110 rows × 1 col: "West", "East", "West", "North", …A range: 10 cells in one column.
G1"West"The value in G1.
C2:C1110 rows × 1 col: "Laptop", "Monitor", "Monitor", "Laptop", …A range: 10 cells in one column.
G2"Monitor"The value in G2.
SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2)1005SUMIFS: Adds values that meet every condition.- sum_range
D2:D1110 rows × 1 col: $1,840, $420, $395, $1,720, … - criteria_range1
B2:B1110 rows × 1 col: "West", "East", "West", "North", … - criteria1
G1"West" - criteria_range2
C2:C1110 rows × 1 col: "Laptop", "Monitor", "Monitor", "Laptop", … - criteria2
G2"Monitor"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2). We ran it in Google Sheets mode on the same data and it returns $1,005.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B11=G1)*(C2:C11=G2)*D2:D11) | $1,005 | Multiplying the tests is the same AND logic and works in every version. |
Common mistakes
- Every range must be the same size as the sum range, or SUMIFS returns #VALUE!.
- SUMIFS joins conditions with AND. For OR (Monitor or Dock), add two SUMIFS together.
- Put conditions in cells, as here, rather than typing them into the formula. Then one formula answers every question.
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 multiple criteria in Excel (SUMIFS)
G3, total the sales for the region in G1 and the product in G2.Questions
How do I use OR in SUMIFS?
Add one SUMIFS per option: =SUMIFS(D2:D11,C2:C11,"Monitor")+SUMIFS(D2:D11,C2:C11,"Dock").
Can I use dates as SUMIFS criteria?
Yes, with an operator joined to a date cell, like ">="&G4. See summing between two dates.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.