CODEWITHSUNDEEP
excelexcel-formula
Bogmadur
Bogmadur

Reputation: 25

excel sum the 25 highest numbers

i have tried to find a formula that will look into the sheet1 colum D where there are allot of numbers. some friends and i are having a competition and we want to sum the points with the top 25 points for each contender.

if we use =SUM(Sheet1!D2:D10000) then all the numbers are summed but wo only want the top 25 numbers. what formula to add?

Upvotes: 0

Views: 375

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Depending on what you want to do with duplicates, try:

 =SUMIF(D2:D10000,">=" & LARGE(D2:D10000,25))

This will return an error if you have fewer than 25 entries.

Upvotes: 0

user8606929
user8606929

Reputation: 225

You can use the LARGE Formula, combined with SUM.

=SUMPRODUCT(LARGE(Sheet1!D2:D10000,ROW(INDIRECT("1:25"))))

If you are using Excel 2019 you can use the SEQUENCE function. It returns a sequence of numbers. We can use it to get the top N values and then sum them up.

Generic Formula:

=SUMPRODUCT(LARGE(range,SEQUENCE(num_values,,[start_num], [steps]))))

Upvotes: 3

Andreas
Andreas

Reputation: 23958

You will need the LARGE function.

=SUM(LARGE(D:D,1),LARGE(D:D,2),LARGE(D:D,3),LARGE(D:D,4)....

and so on

Or you create a separate table with LARGE(D:D, ROW()-n) where n is rownumber -1 the and then pull down (fill down) to get the 25 top and sum them.

Example:

Here I have 50 values and I placed my new table on row 4 so my formula is LARGE(A:A,ROW()-3) which results in the largest value.
Then I can just fill down the 25 values.

enter image description here

Upvotes: 0

Related Questions