The Excel SUMIF() function allows you to add up values conditionally, such as when values are greater than or equal to a provided value. This allows you to, for example, calculate sums that are eligible for bonuses. In this tutorial, you’ll learn how to use the Excel SUMIF() and SUMIFS() functions to add up values based on the values being greater 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 greater than (or greater than or equal to) a value
- How to use the Excel SUMFS() function to add up values greater than or equal to a value and meet another condition
How to Use Excel SUMIF() to Add Values Greater Than a Value
The Excel SUMIF() function allows you to add values conditionally – including adding values that are greater than a given value. The function allows you to use either two or three arguments. 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. Let’s take a closer look at them 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 greater than a value by using the criteria of ">"&value. 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 greater than a value:
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 greater than the value in cell F2 (which, in this case, is 2500). Since we’re adding the same range as the criteria range, we can omit the third argument (the sum range).
How to Use Excel SUMIF() to Add Values Greater Than or Equal To a Value
Similar to adding values that are greater than a value, we can also use the SUMIF() function to add values that are greater than or equal to a value. In fact, this involves only modifying the operator in the criteria.
We can sum values that are greater 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 greater than or equal to a value using the SUMIF() function:
In the example above, we use the SUMIF() function to add cells that are greater than or equal to a value. This can be used if we’re adding values that are eligible for a bonus. For example, if sales that exceed a certain amount are eligible for bonuses, we can easily add these values.
Need to Add Values After a Certain Date?
Since Excel stores numbers as dates, you can easily use the SUMIF() function to add values after 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 greater than a value and meet another condition.
How to Use Excel SUMIFS() to Add Values Greater 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 greater than a value and meet another condition. The SUMIFS() function allows you to check for multiple conditions. The function is written slightly differently than the SUMIF() function. Let’s take a look at it before we dive into an example:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)In the SUMIFS() function, the sum range is the first argument and it’s actually required. 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 greater than a value and where the region is equal to North.
In the example above, we used the following formula to add values equal to or greater 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 greater than or equal to a value.
Conclusion
In this tutorial, you learned how to use the Excel SUMIF() function to add values that are greater than a value. 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 greater than a value as well as greater than or equal to a value. Finally, you learned how to use the SUMIFS() function to add values that are greater than a value and meet another condition.
Additional Resources
To learn more about related topics, check out the resources below: