To sum if between in Excel, you can use the SUMBETWEEN function with two criteria or the built-in SUMIF function.
The SUMBETWEEN function sum values are between the lower and upper threshold. It is a swiss knife, and you can write short and effective formulas.
Syntax
=SUMBETWEEN(rng, lowB, upB, [includeL])Arguments
The function uses three required and one optional argument.
- rng is a range that contains numeric values
- lowB is the lower threshold
- upB is the upper threshold
- includeL is an optional argument; in the case of TRUE (default), the upper and lower bounds will include. Set the argument to FALSE if you want to exclude lower and upper limits.
How to SUM IF between two numbers using the SUMBETWEEN function
Here are the steps to sum values between two numbers:
- Type =SUMBETWEEN(range, 3000,5000)
- Select the range: B3:B10
- Add the lower bound: 3000
- Add the upper bound: 5000
- Press Enter.
- The formula will sum if values are between 3000 and 5000.
SUMBETWEEN is a fast and useful user-defined function in Excel.
SUMBETWEEN function Example
In the example, we have values in the range B3:B10, and we want to sum values between two numbers, so add the criteria:
- Criteria1: value >= 3000
- Criteria2: value <= 5000
Configure the function arguments:
=SUMBETWEEN(B3:B10, 3000, 5000)In this case, (we leave the 4th optional argument default), the formula will also sum the minimum and maximum threshold. The result array:
={3000, 5000, 4000, 4000, 5000} 
The array contains all values that meet both criteria. Finally, the function sums the values in the result array:
=SUM(3000, 5000, 4000, 4000, 5000) = 21000Example 2:
In the second example, you want to exclude the lower and upper limits, so the criteria look like this:
- criteria1: value > 3000
- criteria2: value < 5000
As you see, we replaced the “greater than equal to” and “less than equal to” conditions with “greater than or less than”. Now set the 4th argument to FALSE.
Formula:
=SUMBETWEEN(B3:B10, 3000, 5000, FALSE)Result array: ={4000, 4000}
SUM({4000, 4000} = 8000Sum if between using the SUMIF function
If you are unfamiliar with user-defined functions, the following solution uses a built-in function, SUMIF. First, create a named range, select E3:E10, and add a descriptive name, like “Sales”.
Formula to sum values between two numbers (including the upper and lower threshold):
=SUMIFS(sales,sales,">="&C2,sales,"<="&C3)The formula that excludes the upper and lower threshold:
=SUMIFS(sales,sales,">"&C2,sales,"<"&C3)Implement the SUMBETWEEN function
The function is a part of our free add-in, DataFX. We strongly recommend using it if you want to boost your productivity.
