Sum Greater Than Values (SUMIF-SUMIFS)

puneet-gogia-excel-champs

- Written by Puneet

When managing a small business’s finances, it’s crucial to monitor larger transactions 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 that are greater than 500.

Follow these steps:

  1. Type in =SUMIF( in the cell E2 to start your formula.
  2. 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.
  3. 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”,.
  4. 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.
  5. 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 is the same, you can skip specifying the sum range. And it will still return to you the sum of all the values that are greater than and equal to 500.

You can also use a cell reference to specify the criteria in the function.

How this formula works?

In the above example, we have used the same criteria, but we have referred to a cell that has a number and we want to create greater criteria with this number.

So, in the cell, we have that number, and in the criteria_argument we have combined this number with the greater than and equal operator.

You know the best part of using this method is you can change the number from the cell, and you don’t need to change the actual formula.

There is one more thing that 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, just like we have 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 dynamically. You can see in this example, we have used the value from the cell E1 as a criteria, now when you change the values from the cell from 500 to 600 it will also change that criteria 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 that are 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 (the values in C2:C32). Since multiplying by 1 leaves the original value unchanged and multiplying by 0 results in 0, this effectively filters out values that are not above 500.

Last Updated: May 02, 2024