Table of Contents
The SUMIF function in Excel is a powerful tool that allows you to sum a range of cells based on a single criterion. To use this function, you enter the range of cells to be summed, the criterion, and the sum range. If any of the cells in the range meet the criterion, then the sum of the corresponding cells in the sum range will be calculated. This is a great tool to quickly find the sum of cells that meet a certain criteria.
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, "<100")
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, "<100")
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:
Note: To calculate the sum of values equal to or less than a certain value, use “<=” in the formula instead.