Sum values only once when value listed more than once

Nebula2121

New Member
Joined
Apr 1, 2016
Messages
31
ABC
1NameValueResult
2Apple57
3Broccoli1012
4Broccoli10
5Apple20
6Broccoli10
7Pear50

<tbody>
</tbody>

How do I set up an excel formula for the desired result in Column C? I would like the sum of value for each name to show up once and only have the largest 2 result present. I can do this easily with a pivot, but can't figure out the formula. Thanks!
 
Last edited:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,212
Try:

ABC
1NameValueResult
2Apple57
3Broccoli1012
4Broccoli1
5Apple2
6Broccoli1
7Pear5

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Array Formulas
CellFormula
C2{=IF(COUNTIF($A$1:$A1,A2)>0,"",IF(SUMIF($A$2:$A$7,A2,$B$2:$B$7)>=LARGE(SUMIF($A$2:$A$7,$A$2:$A$7,$B$2:$B$7)*IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-ROW($A$2)+1,1,0),2),SUMIF($A$2:$A$7,A2,$B$2:$B$7),""))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Nebula2121

New Member
Joined
Apr 1, 2016
Messages
31
How would I adjust the formula for the top 4 results? Your help is much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,139
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top