You can use the following formula in Excel to only sum values that are less than a particular value:
=SUMIF(B2:B13, "<"&E1)
This particular formula calculates the sum of values in the range B2:B13 only for the cells that have a value less than the one specified in cell E1.
The following example shows how to use this formula in practice.
Example: SUMIF Less Than in Excel
Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players:
Suppose we would like to calculate the sum of points only for the players who scored less than 20 points.
We can type the following formula into cell E2 to do so:
=SUMIF(B2:B13, "<"&E1)
The following screenshot shows how to use this formula in practice:
We can see that the sum of points for the players who scored less than 20 is 90.
We can confirm this is correct by manually identifying each of these players:
We can confirm that the sum of points for these players is 90:
Sum of Points: 14 + 17 + 14 + 18 + 12 + 15 = 90
Note: If you would like to sum the cells in a range that have a value less than or equal to a specific value, then use the <= symbol in the formula instead:
=SUMIF(B2:B13, "<="&E1)
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use SUMIF Across Multiple Sheets
Excel: How to Use SUMIF with Multiple Ranges
Excel: How to Use SUMIF Before Date
