The Excel SUMIF() function allows you to add up values conditionally, such as when values are less than or equal to a given value. This allows you to, for example, dd values that don’t meet certain sales quotas. In this tutorial, you’ll learn how to use the Excel SUMIF() and SUMIFS() functions to add up values based on the values being less than a value.
By the end of this tutorial, you’ll have learned the following:
- How to use the Excel SUMIF() function to add up values that are less than (or greater than or equal to) a value
- How to use the Excel SUMFS() function to add up values less than or equal to a value and meet another condition
How to Use Excel SUMIF() to Add Values Less Than a Value
The Excel SUMIF() function allows you to add values conditionally, such as when values are less than a certain value. Let’s take a look at how the function is written:
=SUMIF(range, criteria, [sum_range])As you can see from the code block above, the function takes three arguments, though one of them is optional. Let’s take a closer look at the arguments in the table below:
| Argument | Description | Required? |
range= | The range of cells to apply the condition to | Yes |
criteria= | The condition to apply, including any of the logical operators that may be required | Yes |
sum_range= | The range of cells to add up. If empty, Excel adds up the values in the range= argument. | No |
We can use the function to add values that are less than a specific value by writing "<"&value as the criteria. Note that we’re wrapping the condition in double quotes and then joining it with the value that we’re comparing to.
Let’s see how we can use Excel to add values that are less than a value by taking a look at an example:
In the example above, we used the following formula:
=SUMIF(C3:C13, "<"&F2)The formula adds up the values in the range C3:C13 if they are less than the value in cell F2 (which, in this case, is 2500).
How to Use Excel SUMIF() to Add Values Less Than or Equal To a Value
Similar to adding values that are less than a value, we can also use the SUMIF() function to add values that are less than or equal to a value. In order to do this, we only need to change the operator to include cells that are equal to or less than a value.
We can sum values that are less than or equal to a value by using the following formula:
=SUMIF(C3:C13, "<="&F2)Let’s take a look at an example of how to add values that are less than or equal to a value using the SUMIF() function:
In the example above, we modified the SUMIF() function to add values that are less than or equal to a certain value. With this, you can add cells that don’t meet a specific quote, for example.
Need to Add Values Before a Certain Date?
Since Excel stores numbers as dates, you can easily use the SUMIF() function to add values before a certain date. This works in the same way as shown above, even if your cell is formatted as a date!
In the following section, you’ll learn how to use the Excel SUMIFS() function to add values less than a value and meet another condition.
How to Use Excel SUMIFS() to Add Values Less Than or Equal To a Value and Meeting Another Condition
In this section, you’ll learn how to use the Excel SUMIFS() function to add values less than a value and meet another condition. The SUMIFS() function allows you to check for multiple conditions. Because the function is slightly different than the SUMIF() function we’ve used so far, let’s take a look at how it’s written first.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)In the SUMIFS() function, the sum range is now required and has been moved to the first position. This makes sense since we want to have multiple conditions. While the SUMIF() function can omit the sum range argument, SUMIFS() function cannot.
Let’s take a look at an example where we add values equal to or less than a value and where the region is equal to North. The SUMIFS() function is actually case-insensitive. This means that if North is written as north, or NORTH, or another permutation, Excel will include it.
In the example above, we used the following formula to add values equal to or less than the value in cell F2 and equal to North:
=SUMIFS(C3:C13, C3:C13, "<="&F2, B3:B13, "North")In the example above, we use the SUMIFS() function to check for multiple conditions, one of which checks if a value is less than or equal to a value.
Conclusion
In this tutorial, you learned how to use the Excel SUMIF() function to add values that are less than a value. This can be helpful to add values that may not be eligible for additional commissions in the retail sector. You first learned how the function works and how you can add certain conditions to your sums. Then, you walked through practical examples of summing values that are less than a value as well as less than or equal to a value. Finally, you learned how to use the SUMIFS() function to add values that are less than a value and meet another condition.
Additional Resources
To learn more about related topics, check out the resources below: