How do I sum a column if the value in that column is less than a given value?

By /

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)

 

x

Related terms:

  1. How to do Sum If Less Than in Excel?
  2. How do I find the minimum value in excel given multiple criteria?
  3. How do I use conditional formatting if a cell is less than or equal to a value?
  4. What does it mean when a p value is less than 0.001?
  5. # Check if One Column Value Exists in Another Column in Google Sheets
  6. How do I check if one column value exists in another column?
  7. how to Extract Column Value Based on Another Column in Pandas?
  8. Excel: Get the Date of a Given Week Number
  9. How to Find the Probability of A Given B (With Examples)
  10. How to Find Z-Scores Given Area (With Examples)