Results 1 to 7 of 7

Thread: Sum multiple cells to find top three and return a Text

  1. #1

    Sum multiple cells to find top three and return a Text



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

    Hi All,

    I am trying to build a formula to find the top 3 largest names by exposure without using a pivot table - i can't get it to work and have been stewing over this all afternoon. any ideas?


    Name Exposure
    aaaaa 0.0518%
    bbbbb 0.0383%
    ccccc 0.1785%
    ddddd 0.2850%
    fffffff 0.8151%
    gggggg -0.3731%
    aaaaaa 0.9892%
    bbbbbb 0.0051%
    ccccccc 2.7778%
    dddddd -0.0140%
    eeeeee 3.7129%
    fffffff 1.3034%
    gggggg 0.3421%
    aaaaaa -0.0155%
    fffffff 0.0679%
    hhhhhh 0.3542%
    xxxxxx 2.3560%
    yyyyyy 0.4240%
    pppppp -0.0814%
    iiiiii 0.0364%


    Thanks
    Rory

  2. #2
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    114
    Articles
    0
    Excel Version
    365
    Hi,

    I think it's better ff you can share your example into an Excel file.

    However, you can try this, assuming that these data are in columns A and B :

    1st largest name =INDEX(A1:A20,MATCH(LARGE(B1:B20,1);B1:B20,0))

    Good luck

  3. #3
    Quote Originally Posted by hossat View Post
    Hi,

    I think it's better ff you can share your example into an Excel file.

    However, you can try this, assuming that these data are in columns A and B :

    1st largest name =INDEX(A1:A20,MATCH(LARGE(B1:B20,1);B1:B20,0))

    Good luck

    Hi Hossat, thanks for the below but i already tried this formula, yes you're right it will return the largest single name but it won't sum cells with the same name and return the highest - any other suggestions?

    Thanks

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You would need to add a helper column first....

    e.g.

    in C2:

    =SUMIF($A$2:$A$21,A2,$B$2:$B$21)


    copied down.

    Then use the formula Hossat gave you, replacing the B1:B20 range

    e.g

    =INDEX($A$2:$A$21,MATCH(LARGE($C$2:$C$21,ROWS($D$1:$D1),$C$2:$C$21,0))

    copied down as far as needed to get top n names.


  5. #5
    the helper column is useful but your formula above is returning "you have entered too many arguments for this function"
    Any other suggestions?

    Thanks

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Sorry... lost a closing parenthesis in there..

    try:

    =INDEX($A$2:$A$21,MATCH(LARGE($C$2:$C$21,ROWS($D$1:$D1)),$C$2:$C$21,0))


  7. #7
    Quote Originally Posted by NBVC View Post
    Sorry... lost a closing parenthesis in there..

    try:

    =INDEX($A$2:$A$21,MATCH(LARGE($C$2:$C$21,ROWS($D$1:$D1)),$C$2:$C$21,0))

    This works , thanks very much
    Apologies I thought I got back on Friday

Posting Permissions

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