Sum bottom n values

In its simplest form, SMALL will return the "nth smallest" value in a range. For example:

=SMALL(range,1) // smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest

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:

{=SUM(SMALL(B4:B13,{1,2,3}))}

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.