CODEWITHSUNDEEP
excelsumformula
Mayco Duran
Mayco Duran

Reputation: 35

Sum values but avoid repetitions - Excel formula

I need help summing values in a column avoiding to sum twice the ones with same ID.

   ID   |  Value
-----------------
  7001  |   500
  7002  |   200
  7003  |   300
  7001  |   500
  7004  |   300
  7005  |   600
  7003  |   400
  7006  |   100

Expected result: 2000

Can somebody help me with the formula?

Thanks in advance! MD

Upvotes: 0

Views: 57

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

We can use a helper column, in C2 enter:

=IF(COUNTIF($A$2:A2,A2)>1,0,1)

and copy down, then in another cell:

=SUMPRODUCT(B2:B9*C2:C9)

enter image description here

Upvotes: 0

Related Questions