If duplicate value then return just one value and put zero in the other one

Question

Hi everyone,

I'm using an if function to try to return one value and put zeros in the duplicated ones. However, I can't to this second step.

See the example:

As you can see on the yellow rows I have to repeated costs (3500) and 2 repeated capacity tons value (6). So I want to have only ONE cost (green one) and have a zero on the red cell instead of the 3500, IF the capacity tons is less or equal to 18 and times repeated is more than 2.

I didnt add the columns to the picture, so see them as A,B,C and rows 1,2,3 etc.

Answer 1

I'm embarrassed. Here is the next correction:

=IF(NOT(AND(COUNTIF($B$2:$B2, B2)>1,D2<=18)),A2,0)

Answer 2

This formula should do it for you. I'm assuming you want to look at repeat combinations of values in columns A and D:

=IF(NOT(AND(COUNTIF($A$2:$A3, A3)>1,COUNTIF($D$2:$D3, D3)>1)),A3,0)

Answer 3

That formula assumes it is in row 3. If you're starting in row 2, use this and copy down:

=IF(NOT(AND(COUNTIF($A$2:$A2, A2)>1,COUNTIF($D$2:$D2, D2)>1)),A2,0)

Answer 4

Hi Jason!

It didn't work.

I want to have the freight cost just once when the times repeated (column B) is more than two and the capacity tons (column D) is less or equal to 18.

See the image below:

See the green (correct answer)

Thank you very much!!

Answer 5

Sorry, I misunderstood. This should do it for you:

=IF(NOT(AND(COUNTIF($A$2:$A2, A2)>1,D2<18)),A2,0)