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

[rob313]

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.

Thanks in advance!

[NBVC]

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....

[Bob Phillips]

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))),"")

In N2 I added
=SUMIF($K2:$K$145,M$2,$H$2:$H$145)
and copied down

and in O2 I added
=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.

[fuhrung]

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!