The Excel BETWEEN function finds values within a given range and provides useful optional arguments to count, sum, or average values.
In the world of SQL, BETWEEN is a frequently used operator. Unfortunately, the function is missing in Excel, but you can apply user-defined functions to improve your Excel formula toolbox. By default, you can apply some workaround with logical operators and use the built-in COUNTIF and SUMIF functions.
=BETWEEN(range, lower_bound, upper_bound, [include],[calc_type])
The BETWEEN function uses three required and two optional arguments.
- range: the range where the function searches the matching values based on a criteria
- lowB: lower bound is the minimum value
- upB: upper bound is the maximum value
- [include]: set the argument to 0 to exclude limits and set it to 1 to include limits
- [calc_type]: 1 = SUM, 2 = COUNT, 3 = AVERAGE
In the next section, we will show you examples of how the function works.
BETWEEN Function Examples
In the example, the data set is in the range B3:B12. The goal is to apply the SUM, COUNT, and AVERAGE functions between two numbers. At first look, the task looks like a real challenge, but it is not!
The base function is straightforward.
Let us talk about the arguments that control the limits and calculation types.
Let us see the argument pairs. Use the combinations for the 3rd and 4th arguments below to perform various calculations.
The basic formula is:
=BETWEEN(range, min, max, arg1, arg2)
Here is the guide on how to set up the argument pairs to perform different actions:
- SUM cells between two numbers – excludes limits: 0, 1
- SUM cells between two numbers – include limits: 1, 1
- COUNT cells between two numbers – excludes limits: 0, 2
- COUNT cells between two numbers – include limits: 1, 2
- AVERAGE values between two numbers – excludes limits: 0, 3
- AVERAGE values between two numbers – include limits: 1, 3
Note: BETWEEN is a part of our powerful function library, DataFX.