IF Statement to Mark with Yes & Mark Duplicates

I'm assuming this logic:

1. If AE is 1, say Yes
2. If there's a duplicate value, say Yes

Which would be this for row 2:

=IF(OR(AE2=1,COUNTIF(A$2:A$9,A2)>1),"Yes","")

Hi,

There seems to be a problem with this.

It put a "Yes" for the rows that had values of 129 in Column A. These were duplicate values, but did not contain a "1" in Column AE. I would like it to only do those that had one instance of a "1" in Column AE. Thank you for your help!

Originally Posted by alexdi

I'm assuming this logic:

1. If AE is 1, say Yes
2. If there's a duplicate value, say Yes

Which would be this for row 2:

=IF(OR(AE2=1,COUNTIF(A$2:A$9,A2)>1),"Yes","")

I think the logic would be more “if AE is 1 then all of the same number in column A are “Yes”

Cheap and cheerful to use a helper column. See attached.

Originally Posted by alexdi

Cheap and cheerful to use a helper column. See attached.

This works great! Thank you very much! Could you explain to me the logic this formula uses to make this work? Thanks. Reputation added.

Sure. Here's the meat:

=IF(OR(AE2=1,COUNTIF(AF$2:AF$9,A2&"_1")),"Yes","")

So,

Please Login or Register  to view this content.

The helper column AF just exists because I couldn't easily throw the A+AE combination into the COUNTIF function.