Sum If Greater Than and Less Than Cell Value in Excel

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.

Sum If Greater Than and Less Than Cell Value in Excel: 2 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.

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

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.

Read More: How to Use Excel SUMIF with Greater Than Criterion

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.

Read More: How to Use Excel SUMIF to Sum Values Greater Than 0

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.

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.

Read More: How to Use SUMIF to SUM Less Than 0 in Excel

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.

Download Practice Workbook

You can find the practice sheet here.

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.

Related Articles

• How to Sum If Cell Contains Number and Text in Excel
• How to Sum If Cell Contains Number in Excel
• How to Use 3D SUMIF for Multiple Worksheets in Excel
• How to Use Excel SUMIF Function Based on Cell Color

<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel