How to Use Excel SUMIF with Greater Than Criterion: 6 Methods

Method 1 – Excel SUMIF Greater Than Specific Value

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

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

The SUMIF function finds values greater than $2.00 from range C5:C17. Returns the sum of those values in cell D20. C5:C17 is our sum range here.

Method 2 – Excel SUMIF Greater Than Cell Reference

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

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

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. It returns the sum of those values in cell D20.

Method 3 – SUMIF When Criteria Range Is Different in Excel

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

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

The SUMIF function looks for those unit prices that are greater than the value in cell E19 within the range C5:C17. It finds the corresponding total sales of those unit prices within the range E5:E17. Returns the sum of those total sales in cell E20.

Method 4 – Excel SUMIF Greater Than Date

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

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

The SUMIF function first finds sales occur after the date given in cell D19 (which is 07-01-2023) within range B5:B17. It finds the corresponding total sales of those dates within the range D5:D17. Returns the sum of those total sales in cell D20.

Method 5 – Excel SUMIF Greater Than 0

Activate cell D18, insert the formula shown below, and click Enter.

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

The SUMIF function first finds those values greater than 0 within the range D5:D15. It finds the corresponding unit prices of those values within the range C5:C15. Returns the sum of those unit prices in cell D18.

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

First type “=SUMIF(“ in cell C5 in sheet “Different Sheet”. 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)

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.