Excel: A Formula for “Sum If Less Than”

by Tutor Aspire January 17, 2023

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:

Excel sum if less than

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: