Find multiple duplicate ID and sum up other column

[Megat91]

Hello,

im new here, i need to find a formula that might ease my data review.

i want to find a duplicate value in column A, and sum up the value in column C, if the sum up value in column C become "0" then it meet my condition to remark "contra".
The problem is, some of the duplicate value sum up value is not "0" and i want to ignore it.

for example as attach,
Cell A6 & A7 is duplicate, hence i need to sum up cell C6 & C7, and if it become "0" qty, hence it will remark "CONTRA" in cell D6 & D7

unlike

Cell A19 & A20, even it also duplicate, but the total sum up of cell C19 & C20 is not "0", so it will leave it blank in cell D19 & D20


is there any formula to write? because now i just manually filter "duplicate value" then sum up the column C manually and put the remark also manually.

Thanks for the help, really help me as the data is quite big

 

[gaz_chops]

Welcome to the forum, try

=IF(SUMIFS($C$2:$C$21,$A$2:$A$21,A2)=0,"CONTRA","")

 

[Megat91]

Welcome to the forum, try

=IF(SUMIFS($C$2:$C$21,$A$2:$A$21,A2)=0,"CONTRA","")

View attachment 17911

Thanks Mate!..u save my 2 hours time become just 2 click! god bless!

 

[gaz_chops]

You're welcome, thanks for the feedback.

 

[Megat91]

You're welcome, thanks for the feedback.

Hey mate,

sorry..i found problem like this..is there any SUBTITUTE formula can be use to replace those "^" with blank or ignore those "^"

because i just figure out that some of my column A data just got that "^" at the end of the cell data but it represent the same thing as it without "^" sign at the end.

*those in yellow highlight as attached

 

[gaz_chops]

Try

=IF(SUMPRODUCT((SUBSTITUTE($A$2:$A$21,"^","")=SUBSTITUTE(A6,"^",""))*($C$2:$C$21))=0,"CONTRA","")

 

[Megat91]

Try

=IF(SUMPRODUCT((SUBSTITUTE($A$2:$A$21,"^","")=SUBSTITUTE(A6,"^",""))*($C$2:$C$21))=0,"CONTRA","")

View attachment 17944

Thanks mate! it work! really appreciate

 

[gaz_chops]

You're welcome m8.