Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two

Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two

I recently posted two examples of array constants:
how to avoid a lookup table for VLOOKUP, and how to average the 4 highest numbers in a list.

Here’s how you can sum all numbers in a list except for the two lowest or the two highest. In the picture, 9 rounds of bowling were scored, with the 7 highest scores being totaled for each person with this formula in cell B13 copied to cell I13:
=SUM(B4:B12)-SUM(SMALL(B4:B12,{1,2}))

To sum the 5 highest scores (by omitting the 4 lowest scores), change {1,2} to {1,2,3,4}.

If this were a golfing competition, where low scores are favored, then to omit the highest scores in your sum, change the SMALL function to the LARGE function.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Leave a Reply

Your email address will not be published.

*