SUM if between two numbers

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.


=SUMBETWEEN(rng, lowB, upB, [includeL])


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:

  1. Type =SUMBETWEEN(range, 3000,5000)
  2. Select the range: B3:B10
  3. Add the lower bound: 3000
  4. Add the upper bound: 5000
  5. Press Enter.
  6. 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} 
SUM IF BETWEEN two numbers

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) = 21000

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


=SUMBETWEEN(B3:B10, 3000, 5000, FALSE)

Result array: ={4000, 4000}

SUM({4000, 4000} = 8000

Sum 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”.

workaround with SUMIF function

Formula to sum values between two numbers (including the upper and lower threshold):


The formula that excludes the upper and lower threshold:


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.

Related formulas

Istvan Vozar

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