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

I'm not doing well with pasting - use this one instead:

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

Answer 3

Yessssss. Amazing Jason!!! What a great way to start 2018 learning a new excel function.

Thank you very much and have a great new year!!

Diego.