# Sum values only once when value listed more than once

#### Nebula2121

##### New Member
 A B C 1 Name Value Result 2 Apple 5 7 3 Broccoli 10 12 4 Broccoli 1 0 5 Apple 2 0 6 Broccoli 1 0 7 Pear 5 0

<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:

### Excel Facts

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
Try:

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

</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),""))}

</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
How would I adjust the formula for the top 4 results? Your help is much appreciated.

#### Nebula2121

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

Replies
8
Views
72
Replies
5
Views
135
Replies
7
Views
280
Replies
6
Views
88
Replies
4
Views
66