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.
Syntax
=BETWEEN(range, lower_bound, upper_bound, [include],[calc_type])Arguments
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.
Argument examples:
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.
