Exceljet

Quick, clean, and to the point

Sum bottom n values

Excel formula: Sum bottom n values
Generic formula 
=SUMPRODUCT(SMALL(rng,{1,2,n}))
Summary 

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

Explanation 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.