Marking rows with certain duplicate values

**HCLax** · 07-08-2014, 01:34 PM

Hi,

So I am trying to mark certain rows as duplicates if their values in Columns J, AD and ABS(BD) match. J and AD contain Number letter Identification combinations and BD contains a value. I currently have a formula as shows that will mark all of these values:

[Formula]
=IF(AND(COUNTIFS(J:J,J15,BD:BD,ABS(BD15),AD:AD,AD15)>1, (SUMIFS(BD:BD,J:J,J15,AD:AD,AD15,BD:BD,ABS(BD15)))<>ABS(BD15)),"Duplicate","")
[Formula]

The issue with this formula is that I do not want it to mark rows that look like this:

J - AD - BD
AA1 BB2 20
AA1 BB2 -20
AA1 BB2 20

I have a lot of rows that will appear like this. Their sum in BD equals the absolute value of the value in BD. I thought this would get picked up using the SUMIFS portion of my formula but it is not. I cannot find the problem.

Thanks,
Justin

**HCLax** · 07-08-2014, 02:41 PM

So I've discovered that the problem is actually that the SUMIFS will only pick up the positive values in column BD. This means the question becomes is there anyway to have a SUMIFS Formula search to match the absolute value of a value in a column. I cannot simply put ABS(BD:BD) like in the formula below.

=SUMIFS(BD:BD,J:J,J15,AD:AD,AD15,ABS(BD:BD),ABS(BD15))

**Tony Valko** · 07-08-2014, 02:49 PM

Use the SUMPRODUCT function but don't use entire columns as references. Use smaller specific ranges:

=SUMPRODUCT(--(J2:J100=J15),--(AD2:AD100=AD15),--(ABS(BD2:BD100)=ABS(BD15)),BD2:BD100)