Sum Values Less Than a Particular Value (SUMIF Less Than)

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.

In this tutorial, we will learn to write a formula to sum values less than a particular value.

Formula: SUMIF Less Than

You can use the following steps:

1. First, in a cell, enter the SUMIF function.
2. 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.
3. 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.
4. 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.
5. In the end, hit enter to get the result.

=SUMIF(A2:A10,"<40",C2:C10)

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, need to use equals sign as well, just like 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)

SUMIF Less than when you have the same range to check for criteria

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. sumif, is optional. and when you skip it, function will use criteria to sum values.

=SUMIF(C2:C10,"<150")

In the above formula, SUMIF sums values less than 150 from the quantity column.