Microsoft Office Tutorials and References

In Depth Information

**Summing the “top n” values**

Summing the “top
n
” values

In some situations, you may need to sum the
n largest values in a range — for example, the

top ten values. If your data resides in a table, you can use autoﬁ ltering to hide all but the

top
n rows and then display the sum of the visible data in the table’s total row.

Another approach is to sort the range in descending order and then use the
SUM
function

with an argument consisting of the ﬁ rst
n values in the sorted range.

A better solution — which doesn’t require a table or sorting — uses an array formula like

this one:

{=SUM(LARGE(Data,{1,2,3,4,5,6,7,8,9,10}))}

This formula sums the ten largest values in a range named Data. To sum the ten smallest

values, use the
SMALL
function instead of the
LARGE
function:

{=SUM(SMALL(Data,{1,2,3,4,5,6,7,8,9,10}))}

These formulas use an array constant comprised of the arguments for the
LARGE
or
SMALL

function. If the value of
n for your
n calculation is large, you may prefer to use the

following variation. This formula returns the sum of the top 30 values in the Data range. You

can, of course, substitute a different value for 30. Figure 17.13 shows this array formula in

use.

{=SUM(LARGE(Data,ROW(INDIRECT("1:30"))))}

FIGURE 17.13

Using an array formula to calculate the sum of the 30 largest values in a range