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.


=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.

excel between function examples

Argument examples:

Let us see the argument pairs. Use the combinations for the 3rd and 4th arguments below to perform various calculations.

argument configuration of BETWEEN function in EXCEL

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 Ltd. He writes blog posts and helps people to reach the top in Excel.