Sum bottom n values

=SUMPRODUCT(SMALL(rng,{1,2,n}))
To sum the lowest n values in a range, you can use a formula based on the SMALL function and the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain numeric values and n represents the number of lowest values to sum. In the example shown, E5 contains this formula:
=SUMPRODUCT(SMALL(B4:B14,{1,2,3}))
which returns the sum of the three smallest values in B5:B14, 60
In its simplest form, SMALL will return the "nth smallest" value in a range. For example:
However, if you supply an array constant (e.g. a constant in the form {1,2,3}) to SMALL as the second argument , SMALL will return an array of results instead of a single result. For example:
=SMALL(A1:A10,{1,2,3})
will return the 1st, 2nd, and 3rd smallest values in the range A1:A10.
Working from the inside-out in the example shown, SMALL returns the 3 smallest values in the range B5:B14:
=SMALL(B4:B14,{1,2,3})
The result is an array like this:
{10,20,30}
This array is returned directly to the SUMPRODUCT function, which sums the numbers and returns the total:
SUMPRODUCT({10,20,30}) // returns 60
Array formula with SUM
It is common to use SUMPRODUCT as above because it can handle arrays natively without entering as an array formula. However, you can also write an array formula with the SUM function like this:
This is an array formula and must be entered with control + shift + enter, except in Excel 365.
When n becomes large
When n becomes large it becomes tedious to create the array constant by hand – typing out an array constant with 20 or 30 items will take a long time. In this case, you can use a shortcut for building the array constant that uses the ROW and INDIRECT functions. For example, to SUM the bottom 20 values in a range called "rng" you can write a formula like this:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Here, INDIRECT converts the string "1:20" to the range 1:20, which is returned directly to SMALL.
Variable n
To set up the a formula where n is a variable in another cell, you can concatenate inside INDIRECT. For example, if A1 contains N, you can use:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
This allows a user to change the value of n directly on the worksheet.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.