# Sum If Greater Than and Less Than Cell Value in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Excel, you may apply multiple conditions to sum values. From them, the greater than and less than condition is mostly used. You can use both conditions at a time to calculate the sum for a dataset. In this article, we will explore the ways of using formulas to sum if greater than and less than cell value in Excel. You can find the practice sheet here.

## Excel Sum If Greater Than and Less Than Cell Value: Two Cases

To calculate the sum based on greater than and less than conditions you can use the SUMIF function as well as the SUMIFS function. These two functions can sum up values with single or multiple criteria based on both the greater than and less than conditions.

Here, we develop a dataset representing the Salesperson, Units Sold, and Total Sales. From here, we apply different conditions to sum the total of values which will be greater than and less than one or more than one value. ### Case 1: Sum If Greater Than and Less Than from Same Value

If you want to calculate the sum of values greater and less than form a specific value, you can apply both SUMIF and SUMIFS functions. Here we want to calculate the sum of total sales of products sold amount is greater and less than the specific value 10. Both applications are discussed below.

#### 1.1 Applying SUMIF Function for Greater Than and Less Than from Same Value

You have to apply the SUMIF function twice in this formula as SUMIF is applicable for one condition and here we have to show two conditions to calculate the sum. One is greater than the specific value and the other is less than the specific value.

• To measure the sum value for sold units greater and less than the specific value 10, you can apply the following SUMIF formula on cell D18.
`=SUMIF(C5:C15,"<"&D17,D5:D15)+SUMIF(C5:C15,">"&D17,D5:D15)` Formula Breakdown

SUMIF(C5:C15,”<“&D17,D5:D15)+SUMIF(C5:C15,”>”&D17,D5:D15)

• SUMIF(C5:C15,”<“&D17,D5:D15)

The formula calculates the sum of values in the range D5:D15 where the corresponding values in the range C5:C15 are less than the cell value of D17.

Here, C5:C15 represents the range of cells containing the criteria. The “<” symbol denotes that the values in the range should be less than the value in cell D17. The “&” operator is used for concatenation, combining the “<“ symbol with the value in cell D17 to create the complete condition and D5:D15 represents the range of cells to be summed.

Result: 1920

• SUMIF(C5:C15,”>”&D17,D5:D15)

The formula calculates the sum of values in the range D5:D15 where the corresponding values in the range C5:C15 are greater than the cell value of D17.

Here, C5:C15 represents the range of cells containing the criteria. The “>” symbol denotes that the values in the range should be greater than the value in cell D17. The “&” operator is used for concatenation, combining the “>” symbol with the value in cell D17 to create the complete condition and D5:D15 represents the range of cells to be summed.

Result: 5927

• SUMIF(C5:C15,”<“&D17,D5:D15)+SUMIF(C5:C15,”>”&D17,D5:D15)

The results of two SUMIF functions are combined by the plus sign (+).

Result: 7847

You can also use a combination of SUM and SUMIF functions and the formula will look like this.

`=SUM(SUMIF(C5:C15,"<"&D17,D5:D15),SUMIF(C5:C15,">"&D17,D5:D15))`

#### 1.2 Inserting SUMIFS Function to get Sum for both Greater Than and Less Than Condition

To avoid the complexity, you can directly apply the SUMIFS function which supports multiple conditions. As we want to get total sales based on selling units greater and less than 10, the mean selling unit is not equal to 10. For that, we will use the not equal (<>) symbol here.

• The following formula is applicable to this condition and insert that on cell D18.
`=SUMIFS(D5:D15,C5:C15,"<>"&D17)` Formula Breakdown

• SUMIFS(D5:D15,C5:C15,”<>”&D17)

The formula calculates the sum of values in the range D5:D15 that meet the specified criteria. The criteria state that the corresponding values in the range C5:C15 should be not equal to the cell value of D17.

Here, D5:D15 is the range of values that will be summed. C5:C15 is the range of values that will be used as the criteria range and the “<>” symbol denotes “not equal to”. The condition checks if the values in the range C5:C15 are not equal to the value in cell D17. The ampersand “&” operator is used for concatenation, combining the “<>” symbol with the value in cell D17 to create the complete condition.

Result: 7847

### Case 2: Sum If Greater Than and Less Than from Different Values

You can also sum up values greater and less than different values. To do so, you can apply the same SUMIF and SUMIFS functions with modifying formulas.

#### 2.1 Excel SUMIF Function to Get Sum based Greater Than and Less Than From Different Values

To calculate the total sales, here we select product units that are greater than 5 and less than 15. Those conditions are set on cell D18 and cell D17.

• Insert the SUMIF formula for both conditions on cell D19.
`=SUMIF(C5:C15,">"&D18,D5:D15)-SUMIF(C5:C15,">="&D17,D5:D15)` Formula Breakdown

• SUMIF(C5:C15,”>”&D18,D5:D15)

The function sums the values in the range D5:D15 where the corresponding cells in the range C5:C15 are greater than the cell value of D18.

Here C5:C15 represents the range of cells containing the criteria. The “>” symbol denotes that the values in the range should be greater than the value in cell D18. The “&” operator is used for concatenation, combining the “>” symbol with the value in cell D18 to create the complete condition. And D5:D15 represents the range of cells to be summed.

Result: 9042

• SUMIF(C5:C15,”>=”&D17,D5:D15)

The function sums the values in the range D5:D15 where the corresponding cells in the range C5:C15 are greater than or equal to the cell value of D17.

Here, C5:C15 represents the range of cells containing the criteria. The “>=” symbol denotes “greater than or equal to”. The condition checks if the values in the range C5:C15 are greater than or equal to the value in cell D17. The “&” operator is used for concatenation, combining the “>=” symbol with the value in cell D17 to create the complete condition and D5:D15 represents the range of cells to be summed.

Result: 3337

• SUMIF(C5:C15,”>”&D18,D5:D15)-SUMIF(C5:C15,”>=”&D17,D5:D15)

The formula calculates the difference between the sum of values greater than the cell value of D18 and the sum of values greater than or equal to the cell value of D17 in the specified ranges.

Result: 5705

#### 2.2 Use of SUMIFS Function to Get Sum by Setting Up Greater and Less Than Conditions

Using the SUMIFS function, we can apply multiple criteria. Here we will calculate the total sales for the units sold greater than 5 and less than 15.

• Apply the following formula to cell D19.
`=SUMIFS(D5:D15,C5:C15,">5",C5:C15,"<15")` Formula Breakdown

• SUMIFS(D5:D15,C5:C15,”>5″,C5:C15,”<15″)

This formula calculates the sum of values in the range D5:D15 based on multiple criteria specified in the range C5:C15.

Here, D5:D15 represents the range of cells containing the values to be summed.

C5:C15 is the first and second range where the first and second conditions will be evaluated.

“>” & D18, the “>” symbol denotes “greater than”. The first condition checks if the values in the range C5:C15 are greater than the value in cell D18. The “&” operator is used for concatenation, combining the “>” symbol with the value in cell D18 to create the complete condition.

“<” & D17, the “<” symbol denotes “less than”. The second condition checks if the values in the range C5:C15 are less than the value in cell D17. The “&” operator is used for concatenation, combining the “<“ symbol with the value in cell D17 to create the complete condition.

Result: 5705

## Sum If Less than or Equal to a Value in Excel

If you want to calculate a sum that is less than or equal to any specific value, you can easily calculate it by applying the SUMIF function. Here, we want to get the total sales value, where the number of sold units is less than or equal to 10.

• Insert the following formula in cell D18.
`=SUMIF(C5:C15,"<="&D17,D5:D15)` Formula Breakdown

• SUMIF(C5:C15,”<=”&D17,D5:D15)

The formula calculates the sum of values in range D5:D15 that meet the specified criteria. The criteria state that the corresponding values in range C5:C15 should be less or equal to the cell value of D17.

Here, D5:D15 is the range of values that will be summed. C5:C15 is the range of values that will be used as the criteria range and the “<=” symbol denotes “Less or Equal”. The condition checks if the values in the range C5:C15 are less or equal to the value in cell D17. The “&” operator is used for concatenation, combining the “<=” symbol with the value in cell D17 to create the complete condition.

1. Can I use the SUMIF function to sum values greater than or less than a specific cell value?

No, the SUMIF function is limited to a single criterion and cannot directly handle comparisons such as greater than or less than. You will need to use the SUMIFS function to achieve conditional summing based on multiple criteria, including greater than and less than comparisons. But if you use multiple SUMIF functions in a single formula, you can fulfill both conditions.

2. How does the syntax of the SUMIFS function differ from the SUMIF function?

While the SUMIF function has a simple syntax of =SUMIF(range, criteria, sum_range), the SUMIFS function allows for multiple criteria and has a syntax of =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …). It enables you to specify multiple ranges and corresponding criteria for precise summing.

3. Is referring to cell values instead of fixed criteria in the SUMIFS function possible?

Yes, the SUMIFS function allows for dynamic criteria by using cell references. Instead of specifying fixed values, you can refer to other cells that contain the criteria, enabling you to easily update the criteria without modifying the formula.

4. What common errors might I encounter when using SUMIFS for greater than/less than comparisons?

Some common errors include incorrect syntax, using the wrong logical operators (> instead of < or vice versa), and incorrectly defining the criteria ranges. It’s important to carefully check the formula and ensure all elements are correctly specified.

## Things to Remember

• Insert the formula carefully.
• Apply the conditions as per your requirement.
• Develop different SUMIF formulas to apply multiple conditions.
• Input the greater than (>) and less than (<) sign carefully.

## Conclusion

We believe, after completing the article you can sum if greater than and less than cell value easily. You can also learn about the SUMIF and SUMIFS formulas as well. If you have any queries about this article, please comment below. To explore more about Excel you can visit the ExcelDemy website and enrich your knowledge. Annyca Tabassum

This is Annyca Tabassum. I have completed my graduation and post graduation from SUST. Now I am working as a technical writer in SOFTEKO. I love myself and my work very much!

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  