Thread: Need help with a formula to sum and search for top ten values

1. Need help with a formula to sum and search for top ten values

Hey guys, youve helped me out a few times before, hoping you could point me in the right direction here:

I have a data dump with the names of different countries in column K and values associated with each country in column H. I am looking for a formula that will provide a total sum for each country and then return the nine highest countries by value.

2. Have you tried a Pivot table?

You can autolist all the unique countries and there respective sums, as well as list the MAX qty for each and sort from largest to smallest....

3. I added this array formula to M2 and copied down as far as I might need (i went to M30)

=IFERROR(IF(INDEX(\$K\$2:\$K\$145,MATCH(0,COUNTIF(M\$1:M1,\$K\$2:\$K\$145&""),0))=0,"",INDEX(\$K\$2:\$K\$1450,MATCH(0,COUNTIF(M\$1:M1,\$K\$2:\$K\$145&""),0))),"")

=SUMIF(\$K2:\$K\$145,M\$2,\$H\$2:\$H\$145)
and copied down

=RANK(N2,\$N\$2:\$N\$30)
and copie down

I then selected that whole range (M2:O30)< and added a conditional format formula of
=\$O2<10
added a fill colour, and it was done.

4. RANK function with top ten

Hi all. I have a spreadsheet I helped build to track times/ points in a horse show my wife holds annually. I was able to figure out & apply the RANK function, however need it further revised as there are only a max of 10 points given. 10 points for shortest time, 1 point for 10th shortest time, 0 points thereafter, yet if there are only say 7 people that received points, then there would be no 3, 2, or 1 points given. Here is a Dropbox link to the sheet: www dot dropbox dot com/s/ergfiswifglb191/Master%20Entry%20Spreadsheet%202015%20rev5.xlsx?dl=0
Any help would be greatly appreciated!