SUMIF between two numbers in Excel

SUMIF between two numbers in Excel

Discover how to sum values between two numbers in Excel using formulas and the SUMIF, SUMIFS, or SUMBETWEEN user-defined function.

Sum if between using the SUMIF function

The following solution uses a built-in function, SUMIF. First, create a named range, select E3:E10, and add a descriptive name, like “Sales”. This method is great if you want to use built-in functions only.

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

  1. Open Excel.
  2. Type =SUMIFS(E3:E10, E3:E10,”>=”&C2,E3:E10,”<=”&C3)
  3. Press Enter.
  4. The formula returns the sum of values between the upper and lower limits.

Explanation:

SUMIFS function to sum values between two numbers

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

=SUMIFS(sales,sales,”>=”&C2,sales,”<=”&C3)

The formula that excludes the upper and lower threshold:

=SUMIFS(sales,sales,”>”&C2,sales,”<“&C3)

SUMBETWEEN Function

Steps to sum values between two numbers using the SUMBETWEEN function:

  1. Open Excel
  2. Type =SUMBETWEEN(range, 3000,5000)
  3. Press Enter.
  4. The formula will sum if values are between 3000 and 5000.

SUMBETWEEN is a fast and useful user-defined function in Excel.

Syntax and Arguments

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.

Syntax:

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

Arguments:

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 to exclude lower and upper limits.

Example 1:

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

Formula:

=SUMBETWEEN(B3:B10, 3000, 5000)

SUM-IF-BETWEEN-two-numbers

In this case (we leave the 4th optional argument default), the formula will also sum the minimum and maximum threshold. 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 can see, we replaced the “greater than equal to” and “less than equal to” conditions with “greater than or less than.” Now, set the fourth argument to FALSE.

Formula:

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

Result array: ={4000, 4000}

=SUM({4000, 4000} = 8000

If you want to speed up your work, check how UDFs work. Also, take a look at our free add-ins!