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.