Marking Status with criteria

[Raghava@rock]

Hi Team,

I have the 6 products listed and their status in next column , what i looking for is that if the product X status is found as 1 then i need the result to marking as 1 against all the rows where X is listed even though the status of the same against that specific row is not equal to 1.

I have attached the example file

Thank you for your help.

 

[GraH - Guido]

Perhaps this works [E9]=IF(AND(F9="x",VLOOKUP(F9,$F$9:$G$26,2,0)=1),1,"")

 

[bosco_yip]

I think this shorter formula will give the same result.

=IF(F9="x",1,"")

Regards

 

[GraH - Guido]

What if there is no "X" with "1"??? But I find the requirements not that clear neither.

 

[Raghava@rock]

Thank you G that's exactly is my requirement , however i was able to find a way and now stuck with another situation.

I would need formula to get the count of unique text value with criteria. for example Below have the list from which I need unique count of text which has status as "Yes" in this case it will be 3(Apple,Kiwi,Cherry) for TBD status 1(Strawberry) and No Status 2(Orange,Mango)

Thank you.

X

Yes Apple

Yes Apple

Yes Apple

No Orange

No Orange

TBD Strawberry

TBD Strawberry

TBD Strawberry

TBD Strawberry

YesKiwi

YesKiwi

YesCherry

YesCherry

YesCherry

No Mango

No Mango

No Mango