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.
Leave a Reply