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")
Read More: How to Sum If Cell Contains Number and Text in Excel
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)
Read More: How to Sum If Cell Contains Number in Excel
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)
Read More: How to Use Excel SUMIF to Sum Values Greater Than 0
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)
Read More: How to Use SUMIF to SUM Less Than 0 in Excel
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)
Read More: How to Use 3D SUMIF for Multiple Worksheets in Excel
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)
Read More: How to Use Excel SUMIF Function Based on Cell Color
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)
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)
Read More: How to Use Excel SUMIF with Blank Cells
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)
Read More: How to Use SUMIF Function to Sum Not Blank Cells in Excel
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)
Read More: Sum If Greater Than and Less Than Cell Value in Excel
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)Read More: Excel SUMIF Function for Not Equal Criteria
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.
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.
