Sum values only once when value listed more than once

[Nebula2121]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Broccoli[/TD]
[TD]10[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Broccoli[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Broccoli[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Pear[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Eric W]

Try:

	A	B	C
Result

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Value[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Apple[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Broccoli[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Broccoli[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Apple[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Broccoli[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Pear[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nebula2121]

How would I adjust the formula for the top 4 results? Your help is much appreciated.

 

[Nebula2121]

Woohoo. I figured it out. Thanks again for the help!

 

[Eric W]

Glad to help!