Sum if between
Generic formula
=SUMIFS(sum_range,criteria_range,">500",criteria_range,"<1000")
Related formulas
Sum if greater than
Sum if less than
Sum if begins with
Sum if not blank
Sum if cells are equal to
Sum if cells are not equal to
Sum if equal to either x or y
Sum if cells contain specific text
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 1amount,"<1000" // criteria 2
With these criteria, the SUMIFS function sums up all amounts greater than 500 and less than 1000.
Note that both operators (>, <)></)>
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 (<=),></=),>
=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