Forum Discussion
Formulae
How do I find the the sum of the top * numbers in a given range
=SUM(LARGE(B1:B24,ROW(A1:A7)))
Let's say the given range of numbers is in B1:B24 then you can apply this formula to calculate the SUM of the largest 7 numbers. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
If you want the SUM of the largest 9 numbers you can change A1:A7 to A1:A9.
- Sorry, but this doesn't work as the system is picking up A1:A7 as the range A1:A7. I copied and pasted your solution to ensure that I did not make any typos.
=SUM(LARGE(B1:B24,ROW(A1:A7)))
ROW(A1:A7) evaluates to {1,2,3,4,5,6,7} which means that range A1:A7 isn't picked up by the formula. You can actually enter any data in A1:A7 such as formulas, text or numbers and verify that the formula doesn't refer to the entries in this range.
- I see where the problem is, Oliver - my query was not clear enough, so apologies 🥴. I have a spreadsheet (for a weekly golf competition) with a list of names in column A and columns B to AA for scores received. I need to calculate the total of the best 11 scores for each person in column AB. Thanks. Pam