Quick, clean, and to the point

Sum if between

Excel formula: Sum if between
Generic formula 

To sum if between, you can use the SUMIFS with two criteria.

In the example shown, cell G5 contains this formula:


Where "amount" is a named range for cells D5:D11.

This formula sums the amounts in column D when they are greater than 500 and less than 1000.


The SUMIFS function supports Excel's logical operators (i.e. "=",">",">=", etc.), so you can use these as you like in your criteria.

In this case, we want to sum values in column D which match two criteria. The sum range is therefore "amount" (D4:D11), followed by two range / criteria pairs:

amount,">500" // criteria 1
amount,"<1000" // criteria 2

With these criteria, the SUMIFS function sums up all amounts greater than 500 and less than 1000.

Note that both operators (>, <) and threshold amounts are enclosed in double quotes ("").

If you want to include the threshold numbers in the sum (include amounts that equal 500 and 1000), use greater than or equal to (>=) and less than or equal to (<=), like so:


Using a cell reference

If you want to expose the threshold amounts on the worksheet so they can be easily changed, use this formula:


Where A1 is a reference to the lower threshold and B1 is a reference to the upper threshold

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.