# How to sum values between two numbers in Excel

Use SUMIFS with two conditions on the same column: `=SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2)`.

**Excel:** `=SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Deal | Value |  | Min | 5000 |
| Acme renewal | 4800 |  | Max | 20000 |
| Birch upgrade | 12500 |  | Total |  |
| Cobalt new | 20000 |  |  |  |
| Delta add-on | 5000 |  |  |  |
| Ember pilot | 2750 |  |  |  |
| Fjord new | 31400 |  |  |  |
| Granite renewal | 8900 |  |  |  |
| Harbor upsell | 17250 |  |  |  |
| Iris new | 22100 |  |  |  |

Formula in E3: `=SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2)`

Result: $63,650

## 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.

## Other ways

- `=SUMPRODUCT((B2:B10>=E1)*(B2:B10<=E2)*B2:B10)`: The same band written as two tests multiplied together.
- `=SUMIFS(B2:B10,B2:B10,">"&E1,B2:B10,"<"&E2)`: 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.

## 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,""&E2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-values-between-two-numbers