How to sum values between two numbers in Excel
Formula (Excel and Google Sheets)
=SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2)Use SUMIFS with two conditions on the same column: =SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2).
- Result on the example
- $63,650 in
E3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMIFS adds a value only when every condition is true. Two conditions on the same column make a band: at least the minimum and at most the maximum. Both ends are included, so the 5,000 and 20,000 deals count.
=SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2)Result63650
B2:B109 rows × 1 col: $4,800, $12,500, $20,000, $5,000, …A range: 9 cells in one column.
E1$5,000The value in E1.
">="&E1">=5000"The & operator joins as text.
E2$20,000The value in E2.
"<="&E2"<=20000"The & operator joins as text.
SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2)63650SUMIFS: Adds values that meet every condition.- sum_range
B2:B109 rows × 1 col: $4,800, $12,500, $20,000, $5,000, … - criteria_range1
B2:B109 rows × 1 col: $4,800, $12,500, $20,000, $5,000, … - criteria1
">="&E1">=5000" - criteria_range2
B2:B109 rows × 1 col: $4,800, $12,500, $20,000, $5,000, … - criteria2
"<="&E2"<=20000"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2). We ran it in Google Sheets mode on the same data and it returns $63,650.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B10>=E1)*(B2:B10<=E2)*B2:B10) | $63,650 | The same band written as two tests multiplied together. |
| =SUMIFS(B2:B10,B2:B10,">"&E1,B2:B10,"<"&E2) | $38,650Different result | Use > and < when values exactly on the limits should be left out. Leaves out the 5,000 and 20,000 deals: 38,650. |
Common mistakes
- SUMIF takes only one condition.
=SUMIF(B2:B10,">=5000")has no upper limit; you need SUMIFS for both. - If the minimum is larger than the maximum the band is empty and the result is 0, with no error.
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 between two numbers
E3, total the deals worth at least E1 and at most E2.Questions
How do I test one column and add another?
Put the column to add first and test the other: =SUMIFS(C2:C10,B2:B10,">="&E1,B2:B10,"<="&E2).
How do I sum values outside a range instead?
Add the two tails: =SUMIF(B2:B10,"<"&E1)+SUMIF(B2:B10,">"&E2).
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.