How to sum values based on criteria in Excel (SUMIF)
Formula (Excel and Google Sheets)
=SUMIF(B2:B11,G1,D2:D11)Use =SUMIF(criteria_range, criteria, sum_range): it adds the numbers in sum_range on the rows where criteria_range matches.
- Result on the example
- $5,155 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
SUMIF looks down B2:B11 for rows equal to the region in G1, and adds the sales in D2:D11 on those rows. The criteria range and the sum range line up row by row, so they should start on the same row and be the same size.
=SUMIF(B2:B11,G1,D2:D11)Result5155
B2:B1110 rows × 1 col: "West", "East", "West", "North", …A range: 10 cells in one column.
G1"West"The value in G1.
D2:D1110 rows × 1 col: $1,840, $420, $395, $1,720, …A range: 10 cells in one column.
SUMIF(B2:B11,G1,D2:D11)5155SUMIF: Adds values where the range meets a condition.- range
B2:B1110 rows × 1 col: "West", "East", "West", "North", … - criteria
G1"West" - sum_range
D2:D1110 rows × 1 col: $1,840, $420, $395, $1,720, …
- range
In Google Sheets
The same formula works in Google Sheets: =SUMIF(B2:B11,G1,D2:D11). We ran it in Google Sheets mode on the same data and it returns $5,155.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(D2:D11,B2:B11,G1) | $5,155 | SUMIFS puts the sum range first and takes more conditions later, so it's the better habit. |
| =SUMPRODUCT((B2:B11=G1)*D2:D11) | $5,155 | Works when the condition needs a calculation, such as a test on part of the text. |
Common mistakes
- The argument order differs: SUMIF is (range, criteria, sum_range) and SUMIFS is (sum_range, range, criteria). Swapping them returns 0 or the wrong total.
- Text criteria are not case-sensitive, and extra spaces matter: "West " does not match "West".
- Numbers stored as text are not added. If the total looks too small, check for left-aligned numbers.
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 values based on criteria in Excel (SUMIF)
G3, add up the sales for the region in G1.Questions
How do I sum with a partial match?
Use wildcards: =SUMIF(C2:C11,"*Lap*",D2:D11) adds sales for every product containing "Lap".
How do I sum values that are not equal to something?
Use "<>": =SUMIF(B2:B11,"<>West",D2:D11) adds every region except West.
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.