Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    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. #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))),"")

    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.

  4. #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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •