How to Use Excel SUMIF with Greater Than Criterion

In this modern world, life without Excel is kind of unimaginable. Almost in every working sector, Excel works as an essential tool. Numerous operations can be performed through this amazing software. One of its built-in features is the SUMIF function. With the help of this function, you can sum up values from a range of cells based on your given criterion. In this article, we have demonstrated the use of Excel SUMIF with “greater than” operator that is used to specify a criterion within the formula. You may get the output something like as follows.

Overview of Excel SUMIF Function

Excel SUMIF function is used to add up values from a range that matches a given criterion. The syntax of this formula is as follows-

=SUMIF(range, criteria, [sum_range])

The arguments-

range is the range of cells that you want to evaluate by the given criterion.
criteria is the criterion that will be applied to the range.
sum_range is the range of cells from where you want the function to get values and do the summation based on your given criterion. This argument is optional.

Excel SUMIF with Greater Than Criterion: 6 Examples

We will be using the following dataset as an example to illustrate the use of the Excel SUMIF function to calculate the sum of a data range where all the numeric values are greater than a particular value.

1. Excel SUMIF Greater Than Specific Value

Suppose, we want to calculate the sum of those products’ unit prices which are greater than $2.00 from the above dataset.

Select cell D20, apply the formula below, and press Enter.

=SUMIF(C5:C17,">2")

Here, the SUMIF function finds values greater than $2.00 from range C5:C17. Then it returns the sum of those values in cell D20. C5:C17 is our sum range here.

2. Excel SUMIF Greater Than Cell Reference

Previously, we have put the specific value within the SUMIF formula. But we can do the same using cell reference as well.

First, select cell D10, then insert the formula below and hit Enter.

=SUMIF(C5:C17,">"&D19)

Here, the SUMIF function finds the values greater than the value in cell D19 from range C5:C17. We used the ampersand (&) operator to concatenate the “greater than” (>) symbol with the value in cell D19. Then it returns the sum of those values in cell D20.

3. SUMIF When Criteria Range Is Different in Excel

Assume, we want to get the sum of total sales of products whose unit prices are greater than $2.00.

Select cell E20, insert the formula below, and press Enter.

=SUMIF(C5:C17,">"&E19,E5:E17)

First, the SUMIF function looks for those unit prices that are greater than the value in cell E19 within the range C5:C17. Then, it finds the corresponding total sales of those unit prices within the range E5:E17. Finally, it returns the sum of those total sales in cell E20.

4. Excel SUMIF Greater Than Date

Excel’s SUMIF function is great with date criterion as well. Suppose, from the dataset below, we want to calculate the sum of total sales those are generated after 07-01-2023.

Select cell D20, put the below formula and press Enter.

=SUMIF(B5:B17,">"&D19,D5:D17)

Here, the SUMIF function first finds sales occur after the date given in cell D19 (which is 07-01-2023) within range B5:B17. Then, it finds the corresponding total sales of those dates within the range D5:D17. After that, it returns the sum of those total sales in cell D20.

5. Excel SUMIF Greater Than 0

We can use the SUMIF function to calculate the sum of values greater than 0. Let’s say, we have a dataset of products with their unit prices and total units sold. We can see that some of the products are unsold.

What we want to do is to calculate the sum of unit prices of those products that are at least one sold.

First, activate cell D18, insert the formula shown below, and click on Enter.

=SUMIF(D5:D15,">0",C5:C15)

Here, the SUMIF function first finds those values greater than 0 within the range D5:D15. Then, it finds the corresponding unit prices of those values within the range C5:C15. After that, it returns the sum of those unit prices in cell D18.

6. SUMIF Greater Than a Given Value and Extract Data From Different Sheet

If you want to use the SUMIF function to calculate the sum from a different sheet, you have to provide the name of that different sheet within the formula.

For example, let’s say, we want to calculate the sum of total sales of products whose unit prices are greater than $2.00 in sheet “Different Sheet” from sheet “Reference Sheet”.

First type “=SUMIF(“ in cell C5 in sheet “Different Sheet”. Then switch to the sheet “Reference Sheet”.
Select range C5:C17 as the range. Type a comma (,).
Then switch back to the “Different Sheet” and type “>2” which is basically the criteria. Type a comma again.
Again select range E5:E17 from the “Reference sheet” as the sum range and press Enter.
The formula will look like the below-

=SUMIF('Reference Sheet'!C5:C17,">2",'Reference Sheet'!E5:E17)

Excel SUMIF Greater Than or Equal To Criterion

You can do multiple operations using Excel’s SUMIF function. Using Excel’s SUMIF function, you can even add up values from a range that are greater than or equal to a specific number.

Presume, we want to calculate the total sales of those products whose unit prices are greater than or equal to $2.00.

First, select cell E20, insert the formula below, and press Enter.

=SUMIF(C5:C17,">="&E19,E5:E17)

Here, within the formula, we specified the criteria “greater than or equal to” as “>=”. We used the ampersand (&) operator to concatenate the “greater than or equal to” (>=) symbol with the value in cell E19. Eventually, the formula returns the output in cell E20.

In a similar way, if you want to get the sum for less than or equal to a specific value, you may use this “<=” symbol as the “less than or equal to” criteria. The formula may look as follows.

=SUMIF(C5:C17,"<="&E19,E5:E17)

Excel SUMIF Equal to a Value

Let’s say, this time we want to calculate the sum of total sales of products whose unit prices are exactly equal to $2.00.

Select cell E20, apply the formula below, and click on Enter.

=SUMIF(C5:C17,"="&E19,E5:E17)

Similarly, if you want to get the sum for not equal to a specific value, you may use this “<>” symbol as the “not equal to” criteria. The formula will be something like the following.

=SUMIF(C5:C17,"<>"&E19,E5:E17)

Excel SUMIF With Text Criterion

You can use the SUMIF formula with text criterion as well. Suppose, we have a dataset that represents state-wise product sales reports.

What we want to get is the sum of total sales of “Hessen” state only.

Simply select cell C18, insert the formula below and hit Enter.

=SUMIF(C5:C15,"="&C17,D5:D15)

Here, the criterion is set as “=”&C17. The ampersand (&) operator is used to concatenate the “equal to” (=) symbol with the value in cell C17. The formula finds the text in cell C17 within range C5:C15 and then calculates the sum of corresponding values from range D5:D15.

Excel SUMIF Between Two Values

Assume, you got to sum up values between two specific values. Let’s say, from the following dataset, you want to calculate the sum of total sales of those products whose unit prices are between $2 and $5. Excel SUMIF can help you with that also.

Select cell D21, insert the formula below, and press Enter.

=SUMIF(C5:C17,">"&D19,E5:E17)-SUMIF(C5:C17,">="&D20,E5:E17)

Formula Breakdown

SUMIF(C5:C17,">"&D19,E5:E17)

First, the formula calculates the sum of products’ total sales whose unit prices are greater than $2.

Result: 4422.91

SUMIF(C5:C17,">="&D20,E5:E17)

After that, this formula measures the sum of those products’ total sales whose unit prices are greater than or equal to $5.

Result: 335.52

SUMIF(C5:C17,">"&D19,E5:E17)-SUMIF(C5:C17,">="&D20,E5:E17)

Finally, the whole formula will return the difference between the first calculated value and the second calculated value.

Result: 4087.39

Alternatively, you can use the SUMIFS function to perform this task. You can use the below formula as well for the same output.

=SUMIFS(E5:E17, C5:C17, ">"&D19, C5:C17, "<"&D20)

Frequently Asked Questions

1. What is SUMIF <> in Excel formula?

The logical operator “<>” stands for “not equal to”. It lets you add up the values in an array of cells that don’t meet a specific criterion used within the SUMIF function.

2. How do you use COUNTIF for greater than?

The COUNTIF function returns the number of cells that matches the specified criterion given in the formula.

For example, you have some values within the range A1:A5 and those are 10, 20, 30, 40 & 50. Now if you select an empty cell and apply a formula such as “COUNTIF(A1:A5,“>30”)”, it will return 2. Because only two cells within range A1:A5 contain values greater than 30 and those are 40 and 50.

3. What is the difference between SUMIF and SUMIFS?

The SUMIF function works only for a single criterion whereas the SUMIFS formula can be used for multiple criteria.

4. Can you SUMIF with two criteria?

Actually no, you can’t use the SUMIF function with two criteria. If you want to set multiple criteria, you have to use the SUMIFS function instead which is basically an advanced version of the SUMIF function.

Download Practice Workbook

Download this practice workbook to have a better understanding.

SUMIF Greater Than.xlsx

Conclusion

With the help of Excel’s SUMIF function, you may sum up a set of cells based on predefined criteria. The “greater than” condition is used within the SUMIF formula when one needs to calculate the sum of values from a range that is greater than a specific value. The SUMIF function may assist you with quick and simple data analysis so you can learn more about your company or organization, regardless of whether you are working with sales data, financial data, or any other form of data. Hope, you learned about the use of Excel SUMIF with “greater than” condition.

<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!