You can use the SUMIF and SMALL functions to sum the bottom three numbers in a data range. You can change the bottom three to any other numbers, such as the bottom five or bottom ten numbers.
Example: You are working with a worksheet with the product in column A, the amount in column B, and the profit/loss in column C.
Question: What is the sum of the bottom three numbers in column B?
=SUMIF(B2:B10,"<="&SMALL(B2:B10,3))
The result returns 1,100.
Explanation:
- Step 1: SMALL (B2:B10,3): To have the third smallest number, which is 300;
- Step 2: "<="& SMALL (B2:B10,3): The condition to have the bottom three numbers;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array and the range B2:B10.
However, this may be problematic if the count of the bottom values is more than the count you specified. For example, in this example, you have one 200 and three 300. The total value of 300 or less is 200 + 300 + 300 + 300 = 1,100.
When the number of values is more than the numbers you specified, it is better to check the total count first and modify the formula accordingly. In this example, the bottom three values should be one 200 and two 300.
=MIN(B2:B10) + SMALL(B2:B10,2) + SMALL(B2:B10,3)
The result returns 800.
Notes: The SUMIF Function
The SUMIF function sums the values in a range that meets the specified criteria.
Formula:
=SUMIF(range, criterion, [sum_range])
Explanations:
– Range is required; the cells you want to be evaluated by criteria.
– Criterion is required; the criteria define which cells will be added.