Adding unique values

Question

Community,

In Excel, I am trying to add values that are unique only. This is an example:

Acct #             Value$
9xxx123            40
9xxx123            15
9xxx123            40
9xxx123            21
9xxx123            15
9xxx234            10
9xxx234            08
9xxx234            10

My data has repeated values and I am trying to only add the unique values for the same accounts. For example, on the data above my answer should look like:

9xxx123 = 76
9xxx234 = 18

Thanks!

Answer 1

Use:

=SUMPRODUCT((($A$2:$A$9=E2)*$B$2:$B$9)/(COUNTIFS($A$2:$A$9,E2,$B$2:$B$9,$B$2:$B$9)+($A$2:$A$9<>E2)))