To sum if between in Excel, we need to use the SUMIFS Function and define our criteria. In this example, we will use the SUMIFS Function to sum all of the values between two specific values. We will also use a named range to demonstrate how this can used in place of a standard range of cells.
=SUMIFS(sum_range,criteria_range,">25000",criteria_range,"<65000")
Syntax Explanation:
- sum_range – Range of cells you want to sum.
- criteria_range – Range of cells for criteria match.
With the SUMIFS Function, we can use a variarty of logical operators. These include “=”,”>”,”>=”, “<“, “<=” etc.
Usage Demonstration
Let’s look at the following data set. We want to sum all of the values greater than 25000 but less than 65000. This is achieved by using the following formula.
=SUMIFS(sum_range,criteria_range,">25000",criteria_range,"<65000")
Sum if between with Named Range
In this example, we will use a named range to instead of defining the cell range. This is done by creating a Named Range. The easiest way this is achieved is to highlight your range of cells, or cell, then enter a name in the box next to the formula bar. Please note that this name must not contain any spaces. For a complete understanding, refer to the named range tutorial.
Once named, we can use “Sales_dollars” in our formula as shown.
=SUMIFS(sales_dollars,sales_dollars,">25000",sales_dollars,"<65000")
