Excel: A Formula for “Sum If Less Than”

You can use the following formula in Excel to sum all values in a range that are less than a particular value:

=SUMIF(B2:B13, "

This particular formula sums all of the values in the range B2:B13 that are less than 100.

The following example shows how to use this formula in practice.

Example: Use “Sum If Less Than” in Excel

Suppose we have the following dataset in Excel that contains information about sales made by various employees at some company:

We can use the following formula to sum all of the values in the Sales column that are less than 100:

=SUMIF(B2:B13, "

The following screenshot shows how to use this formula in practice:

We can see that the sum of the values in the Sales column that are less than 100 is 168.

Note that you could also use a cell reference in the formula.

For example, we could use the following formula to sum the values in the Sales column that are less than the value in cell D2:

=SUMIF(B2:B13, "D2)

The following screenshot shows how to use this formula in practice:

We can see that the sum of the values in the Sales column that are less than 100 is 168.

Note: To calculate the sum of values equal to or less than a certain value, use “ in the formula instead.

The following tutorials explain how to perform other common tasks in Excel: