What is the Excel formula for finding the sum of a range of cells if the value is less than a specific number?

By /

The Excel formula for finding the sum of a range of cells if the value is less than a specific number is the SUMIF function. This function allows you to specify a criteria or condition, in this case being a value less than a specific number, and only sums the cells that meet that criteria. The syntax for the SUMIF function is =SUMIF(range, criteria, [sum_range]), where the range is the cells to be evaluated, the criteria is the condition or value to be checked, and the sum_range (optional) is the cells to be summed if they meet the criteria. This formula is useful for quickly calculating sums for a specific subset of data based on a specific condition.

Excel Formula: Sum If Less Than

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:

Excel sum if less than

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

=SUMIF(B2:B13, "<="&E1)

Additional Resources

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

x

Related terms:

  1. How do I sum a column if the value in that column is less than a given value?
  2. How can I write an Excel formula that checks if a value is greater than one number but less than another?
  3. How can I sum a range of cells in Excel if the corresponding date is greater than a certain date?
  4. How can I utilize the colMax function in R for finding the maximum value in a specific column?
  5. How can I use the COUNTIF function in Excel to count cells that do not contain a specific value?
  6. How do I construct an Excel formula to return a specific value if a cell is between two numbers?
  7. Excel Formula: If Greater Than But Less Than
  8. How do I use Google Sheets to count the number of cells that are greater than a certain number but less than another number?
  9. How to do Sum If Less Than in Excel?
  10. How to Calculate Sum If Cells Not Equal to Value?