BETWEEN function

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:

1. SUM cells between two numbers – excludes limits: 0, 1
2. SUM cells between two numbers – include limits: 1, 1
3. COUNT cells between two numbers – excludes limits: 0, 2
4. COUNT cells between two numbers – include limits: 1, 2
5. AVERAGE values between two numbers – excludes limits: 0, 3
6. AVERAGE values between two numbers – include limits: 1, 3

Note: BETWEEN is a part of our powerful function library, DataFX.

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.