Microsoft Office Tutorials and References

In Depth Information

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

These formulas use an array constant comprising the arguments for the LARGE or SMALL function. If the

value of
n
for your top-
n
calculation is large, you may prefer to use the following variation. This formula re-

turns the sum of the top 30 values in the
Data
range. You can, of course, substitute a different value for 30. Fig-

ure 7-15 shows this array formula in use.

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

See Chapter 14 for more information about array constants.

Figure 7-15:
Using an array formula to calculate the sum of the 30 largest values in a range.

Conditional Sums Using a Single Criterion

Often, you need to calculate a conditional sum. With a
conditional sum,
values in a range that meet one or more

conditions are included in the sum. This section presents examples of conditional summing using a single cri-

terion.