To sum the bottom n values in a range in Excel, you can use a formula based on the SUM and SMALL functions.
How to sum bottom n values in Excel
Here are the steps to sum the bottom n values in a range:
- Open Excel.
- Type =SUM(SMALL(range, {1,2,…,n}))
- Press Enter.
- The formula will return the sum of the bottom n values in the range.
The SMALL function retrieves the smallest “n” values in the specified range without sorting the entire list. You need to replace {1,2,…,n} with an array representing the top “n” smallest values.
Formula:
=SUM(SMALL(range, {1,2,…,n}))
Explanation
- SMALL(range, {1,2,…,n}):
- The SMALL function retrieves the smallest specific values from the range.
- {1,2,…,n} is an array constant, where each number represents the position of the smallest values you want to retrieve (e.g., {1,2,3} would pick the smallest, second smallest, and third smallest values). For instance, if “n” is 3, SMALL(range, {1,2,3}) will return the three smallest numbers in that range.
- SUM(…): SUM then adds up the values retrieved by SMALL, giving you the sum of the smallest n numbers in the specified range.
This formula is useful when you need the aggregate of the smallest values, such as calculating the lowest scores or minimum values in a dataset.
Example
In the example, we have a range B2:E6 and want to find and summarize the smallest three values.
Formula:
=SUM(SMALL(B2:E6, {1,2,3}))
For the sake of simplicity, I’m using the SMALL function as a standalone.
In Excel, the SMALL function selects the smallest values within a specified range based on a given position or array of positions. It requires two arguments: the range of cells to check and the position or array of positions of the smallest values to retrieve. In this case, SMALL(B2:E6, {1,2,3}) extracts the first, second, and third smallest values from the range B2 to E6, which are 11, 25, and 27, respectively.
The {1,2,3} part is an array that specifies which positions to consider from the smallest values. Thus, we are looking for three distinct values from among the smallest, returned by the SMALL function. The SUM function then summarizes these results to get the total of the three smallest values.
