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.

**Download Practice Workbook**

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.

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

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