To sum values below a particular value, you need to use the SUMIF function in Excel.
You need to specify the range of cells to check for the criteria, a value to use as criteria, and then the range from where you want to sum values. But you can also use the same range to sum values and check for criteria.
As I said, SUMIF is great for adding up values that are less than a specific value because it’s easy to use and very efficient.
It allows you to quickly specify a condition and sum only those numbers that meet the condition, all within a single, simple formula. And in this tutorial, we will learn to write a formula to sum values less than a particular value.
SUMIF Less Than (Sum Less than Values)
SUMIF is used to add up values in a range that meets a single criteria. When you want to sum values that are less than a specific value, you can specify this in the criteria of the SUMIF.
=SUMIF(A2:A10,"<40",C2:C10)
You can use the following steps:
- First, in a cell, enter the SUMIF function.
- After that, in the first argument, refer to the range where you need to check for the criteria. In our example, you need to refer to the range of lot sizes.
- Now, you need to specify the criteria here. So, we will use a 40 with a lower than operator to sum all the less than values.
- Next, we will refer to the range where we have values to sum. In our example, you need to refer to the range of quantity. In the end, hit enter to get the result.
As you can see, we have got 685 in the result. That means the sum of all the values from the quantity column where the value in the lot size column is less than 40 is 685.
When you use a lower than sign with the value, it doesn’t include that value while sum less than values. That means with <40, you don’t have the value 40 included. For this, you need to use the equals sign as well, just like the following.
=SUMIF(A2:A10,"<=40",C2:C10)Using the Less Than Value from a Cell
Let’s say instead of specifying the value into the function, you want to use for a cell. In that case, you need to write your formula like the following example:
Here you need to use the lower than sign using double quotation marks, and then by using an ampersand combine it with the cell in which you have the less than value.
=SUMIF(A2:A10,"<"&E5,C2:C10)- A2:A10: This is the range of cells that the function will check according to the criterion entered. The function will look at the set of cells to determine whether to include the corresponding value from the third argument (C2:C10) in the sum.
- “<“&E5: This is the function’s criterion to decide whether to sum the corresponding value from the third argument. In this case, the formula checks whether the value in the range A2:A10 is less than in cell E5. The “<” means less than, and the “&” concatenates this operator with the value in E5 to create the entire criterion.
- C2:C10: The function will sum up the actual range of cells based on the criterion. For each cell in the range A2:A10 that meets the criterion (i.e., is less than the value in E5), the function will include the corresponding cell from the range C2:C10 in the sum.
- Ensure no extra spaces are in the concatenation; ” < ” will not work correctly, it should be “<“.
- The cell reference in the criteria allows you to change the value in C1 without altering the formula.
SUMIF Less than When You have a Same Range to check for Condition
Now let’s think about another situation where you have the same range to sum the values and to check the less than criteria.
In the above example, we have referred to the quantity column in the criteria_range, and in the criteria have specified <150, but we have specified no range in the sum_range argument. In SUMIF, sum_range is optional. And when you skip it, the function will use the criteria range to sum values.
=SUMIF(C2:C10,"<150")In the above formula, SUMIF sums values less than 150 from the quantity column.
Get the Excel File
Related Formulas
- Sum Greater than Values using SUMIF
- Sum Not Equal Values (SUMIFS) in Excel
- SUMIF / SUMIFS with an OR Logic in Excel
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- SUMIF By Date (Sum Values Based on a Date)
- Back to the List of Excel Formulas