SUMIFS Between Two Numbers: Sum Values in a Range

To use SUMIFS between two numbers, you check the same column twice: once for the lower limit and once for the upper limit. =SUMIFS(D2:D8,C2:C8,">=5",C2:C8,"<=10") adds the revenue of every order with 5 to 10 units. This guide shows the formula step by step, how to include or exclude the limits, how to keep the limits in cells, and how to build bands for a summary table.

Why SUMIF can't sum between two numbers#

SUMIF takes one condition, and "between" is really two: at least the lower limit, and at most the upper limit. You can't squeeze both into one criteria string. ">=5<=10" is not a valid condition, so Excel compares against that exact text and returns 0.

SUMIFS takes as many conditions as you need, and a row is added only when all of them are true. That's exactly what "between" means.

All the examples use this order list in A1:D8:

How to sum between two numbers with SUMIFS#

Put the column to add first, then the column to check twice, with a different limit each time:

=SUMIFS(D2:D8,C2:C8,">=5",C2:C8,"<=10")

Orders O-102 (8), O-104 (5), O-105 (10) and O-106 (7) pass both tests. The result is 3,600 (960 + 600 + 1,200 + 840).

The operator goes inside the quotes, touching the number. The SUMIFS function guide lists every argument, and the guide to COUNTIFS and SUMIFS with multiple conditions covers other combinations.

Sum the numbers themselves#

If you want to add the values you're checking, use the same column as the sum range:

=SUMIFS(C2:C8,C2:C8,">=5",C2:C8,"<=10")

This adds the units from 5 to 10: 8 + 5 + 10 + 7 = 30.

Including or excluding the limits#

"Between" can mean two things, so choose the operators on purpose:

The difference is only the rows sitting exactly on a limit, but in a test that changes the answer. If the question says "from 5 to 10", include both ends.

How to put the limits in cells#

Keep the limits in cells when you want to change them without editing the formula. If G1 holds 5 and G2 holds 10:

=SUMIFS(D2:D8,C2:C8,">="&G1,C2:C8,"<="&G2)

The & joins the operator text to the number in the cell, building ">=5" and "<=10". The result is again 3,600. Don't write ">=G1": inside the quotes, G1 is just two letters, and the formula returns 0.

This joining rule is the same one used for text and wildcard criteria, which you can practise in the criteria and wildcards lesson. Wildcards like * only work on text, though. For numbers, "between" always needs two comparisons.

Between two numbers plus another condition#

Add more pairs to narrow it down. To total North orders with 5 to 10 units:

=SUMIFS(D2:D8,B2:B8,"North",C2:C8,">=5",C2:C8,"<=10")

Only O-106 (North, 7 units) passes all three tests, so the result is 840. North's 3-unit and 12-unit orders fall outside the range.

The order of the pairs doesn't matter. What matters is that every criteria range is the same size as the sum range, or SUMIFS returns #VALUE!.

How to sum values in bands#

A band table splits all the values into ranges, such as small, medium and large orders. Put the lower limits in one column and the upper limits in the next. With the lower limit in G5 and the upper in H5:

=SUMIFS($D$2:$D$8,$C$2:$C$8,">="&G5,$C$2:$C$8,"<="&H5)

Fill it down for each band:

The bands add up to the full total, 7,080, which proves no order was missed or counted twice. For values with decimals, such as 9.5, whole-number bands leave gaps. Use ">="&G5 with "<"&G6 (the next band's start) instead, so every value lands in exactly one band. The guide to COUNTIF between two numbers builds the same bands with counts.

Other ways to sum between two numbers#

SUMIFS is the clearest option, but you'll see two others in older files:

Subtracting two SUMIFs. Add everything up to 10, then take away everything below 5:

=SUMIF(C2:C8,"<=10",D2:D8)-SUMIF(C2:C8,"<5",D2:D8)

That's 3,960 − 360 = 3,600. It works in any Excel version but is harder to read.

SUMPRODUCT. Each comparison returns TRUE or FALSE, and multiplying them keeps only rows where both are TRUE:

=SUMPRODUCT((C2:C8>=5)*(C2:C8<=10)*D2:D8)

This also returns 3,600. It's useful when you need a calculation SUMIFS can't do inside the condition.

To sum values outside a range, such as under 5 or over 10, add two SUMIFS: =SUMIFS(D2:D8,C2:C8,"<5")+SUMIFS(D2:D8,C2:C8,">10") returns 3,480. The guide to SUMIFS with OR logic explains this pattern. For dates, the method is the same, because dates are numbers: see summing between two dates.

Common mistakes with SUMIFS between two numbers#

These slips cause most zero or wrong totals:

1. Both limits in one string. ">=5<=10" returns 0. Use two pairs.
2. Limits the wrong way round. ">=10" with "<=5" can never both be true, so the result is 0.
3. A cell reference inside the quotes. Write ">="&G1, not ">=G1".
4. Numbers stored as text. Imported units that are text don't pass ">=5". Convert them with VALUE.
5. Overlapping bands. Bands like 1–5 and 5–10 count 5 twice. Check that the bands add up to the total.

Practise it#

Two-limit SUMIFS is a classic Excel test question. Practise COUNTIFS and SUMIFS in a live spreadsheet: you build multi-condition totals and the sheet checks each formula instantly. Then sharpen your criteria with operators, cells and wildcards in the criteria and wildcards lesson.