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