Sum if between in Excel

This tutorial shows how to Sum if between in Excel using the example below;

Formula

=SUMIFS(sum_range,criteria_range,">500",criteria_range,"<1000")

Explanation

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

In the example shown, cell G5 contains this formula:

=SUMIFS(amount,amount,">500",amount,"<1000")

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.

How the formula works

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:

=SUMIFS(amount,amount,">=500",amount,"<=1000")

Using a cell reference

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

=SUMIFS(amount,amount,">="&A1,amount,"<"&B1)

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