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

1. ## Sum multiple cells to find top three and return a Text

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. 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. Originally Posted by hossat
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. 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. 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. 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. Originally Posted by NBVC
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
•