When managing a small business’s finances, monitoring larger transactions is crucial to ensure budget control.
For example, from a given month’s expense data, you should sum all the transactions that exceed $500 to scrutinize significant expenditures.
In this data, expenses like “Computer Hardware” at $800, “Software Subscription” at $550, “Legal Services” at $1200, “Office Furniture” at $600, “Conference Tickets” at $1200, and “Travel Expense” at $750 would be included in this sum.
Here, SUMIF or SUMIFS are excellent ways to sum values greater than a given value because these functions are designed to add numbers that meet specific conditions.
SUMIF for Sum Greater than Values
The SUMIF function can add numbers from a range that meet a specific condition. It works by looking at a range of cells to check if they meet the given criterion (like “greater than $500”).
=SUMIF(C2:C21,">=500",C2:C21)
You can enter the above formula into cell E2 to get the sum of values greater than 500.
Follow these steps:
- Type in =SUMIF( in the cell E2 to start your formula.
- The first argument is the range where the condition is checked. In our example, it’s C2:C21, where we have values. So, type C2:C21.
- The second argument is the condition. We need to sum values greater than or equal to 500, so it’s “>=500”, which means the function looks for values greater than or equal to 500. Enter “>=500”,.
- The third argument is the range from which to sum the values. Again, it’s C2:C21 in our example. Type C2:C21 and then a closing parentheses.
- In the end, hit enter to get the result.
If you need to change the criteria value from $500 to another value, edit the “>500” part of the formula to use the new value (e.g., “>1000” for values over $1000).
Note: As your sum range and criteria range are the same, you can skip specifying the sum range. And it will still return to you the sum of all the values greater than and equal to 500.
You can also use a cell reference to specify the criteria in the function.
How does this formula work?
In the above example, we used the same criteria, but we referred to a cell with a number and want to create greater criteria with this number.
So, we have that number in the cell, and in the criteria_argument, we have combined this number with the greater than and equal operators.
The best part of using this method is that you can change the number from the cell without changing the actual formula.
One more thing you can change in the formula is that you can use absolute reference in the reference to the criteria range.
For this, you just need to add dollar signs before the cell reference, as in the following example.
SUMIFS for Sum Greater than Values in Excel
Let’s take an example of a different scenario where you need to manage inventory expenses and specifically want to track how much is being spent on high-cost items across various product categories.
You need to identify the total sales of products in the “Electronics” and “Clothing” categories that have sold for more than $100 each.
=SUMIFS(C2:C32, B2:B32, "Electronics", C2:C32, ">100")
Hard Value Vs. Cell Reference in SUMIF/SUMIFS
When writing a formula, you can choose between hard values and cell references, each serving different purposes.
Hard values are actual numbers directly entered into your formula, like “>100” in a SUMIFS function. These values make your formula easy to write and read but less flexible because any change in criteria requires editing the formula itself.
On the other hand, using a cell reference (like B2 or C32) allows your formula to use the values in those cells dynamically. In this example, we have used the value from cell E1 as a criterion. Now, when you change the values from the cell from 500 to 600, it will also change that criterion in the formula.
Use SUMPRODUCT to SUM Values Greater than a Given Value
It is a bonus formula that I want to share with you. In this formula, instead of the SUMIF, you can use SUMPRODUCT to sum values greater than a value.
=SUMPRODUCT(--(C2:C21>=500), C2:C21)
In this formula, “C2:C32 >=500” creates an array of Boolean values (TRUE or FALSE) where each value in the range C2:C32 is compared to 500. TRUE if the condition is met (value is greater than 500); otherwise, it is FALSE. And then, the double hyphen (–) converts the array of TRUE/FALSE values into 1s and 0s. TRUE becomes 1, and FALSE becomes 0.
Then, SUMPRODUCT multiplies each value of the first array (1s and 0s) with the corresponding values in the second array (C2:C32). Since multiplying by 1 leaves the original value unchanged and multiplying by 0 results in 0, this effectively filters out values not above 500.
Related Formulas
- Sum Not Equal Values (SUMIFS) in Excel
- SUMIF / SUMIFS with an OR Logic in Excel
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- Combine VLOOKUP with SUMIF
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- SUMIF By Date (Sum Values Based on a Date)
- Back to the List of Excel Formulas