In this tutorial, you’ll learn how to use the Excel SUMIFS() function to add values between two numbers. This can be incredibly useful when you need to sum values in a given range, such as to calculate different commission totals. For example, sales that are between $1,000 and $2,000 may be eligible for different commissions than other sales.
By the end of this tutorial, you’ll have learned the following:
- How to use the Excel SUMIFS() function to add values between two numbers
- How to use the Excel SUMIFS() function to add values between two numbers that also meet another condition
- How to use the Excel SUMIFS() function to add values between two numbers in a different column
Excel SUMIFS() to Add Values Between Two Numbers
We can use the Excel SUMIFS() function to add values between two numbers. This is because the function allows you to sum values based on one or more criteria.
How can you add values between two numbers in Excel?
To sum values between two numbers in Excel, you can use the Excel SUMIFS() function like =SUMIFS(values, values, “>=”&lower, values, “<“&upper). This will ensure that all values fall within the range between lower and upper bounds.
Let’s take a look at an example of how to add values between two numbers in Excel. In the example above, we’ll include the lower bound and exclude the upper bound.
In the example above, we used the following formula:
=SUMIFS(D3:D13, D3:D13, ">="&G2, D3:D13, "<"&H2)The formula adds values in the range D3:D13 that are greater than or equal to the value in cell G2 (in this case, 2000). It also adds values that are less than the value in cell H2, which in this case is 3000. To change whether lower and upper bounds should be included, you can simply change the operator used in the criteria.
Excel SUMIFS() to Add Values Between Two Numbers and Another Condition
In this section, we’ll build on what you learned above. We’ll sum values that are between two numbers and also meet another condition.
Specifically, we’ll add numbers between 2000 and 3000, but only from “North” region. This can be particularly helpful when you need to calculate which areas or salespeople are eligible for a particular sales commission, for example.
In order to do this, we’ll add a third condition that evaluates whether the value in range C3:C13 is equal to “North”. Let’s see what this looks like in Excel:
In order to accomplish this, we simply added the following arguments to the previous example: ..., C3:C13, G3), where the value of the region is stored in cell G3. This is a great example of how we can use Excel to sum values that are in a different column than our values.
In the following section, you’ll learn how to sum values between two numbers if a different column.
Excel SUMIFS() to Add Values Between Two Numbers in a Different Column
In this section, you’ll learn how to sum values between two numbers that are in a different column in Excel. For example, we’ll add up the sales of people between the age of 20 and 39. This allows you to sum values in one column, based on meeting conditions in another column.
Let’s see what this looks like in Excel:
We’re using the following formula to add values that are between two numbers in a different column:
=SUMIFS(D3:D13, B3:B13, ">=20", B3:B13, "<=39")In this formula, we’re adding values in column D. However, we’re adding values based on criteria in column B, our Age column. Note that while we are using numeric values as our condition, we need to wrap our values in double quotes, since we are using an operator with them.
Conclusion
In this tutorial, you learned how to add values between two numbers in Excel using the SUMIFS() function. This allows you to add values that may fall within a specific commission rate, for example.
You first learned how to add values between two numbers, as well as how to add values between two numbers that also meet another condition. From there, you learned how to add values between two numbers that are stored in a different column. In this example, we added values that were in a specific age group. This allowed you to calculate the different totals of sales for a specific group.
Additional Resources
To learn more about related topics, check out the resources below: