Sum If Greater Than and Less Than Cell Value in Excel

Method 1 – Sum If Greater Than and Less Than from Same Value

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.

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.

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.

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. We will use the not equal (<>) symbol here.

The following formula applies 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 not be equal to the cell value of D17.

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 those 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

Method 2 – Sum If Greater Than and Less Than from Different Values

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

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.

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.

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. 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.

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. 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.

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.

Frequently Asked Questions

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.