IF Formula Involving Non-adjacent Cells Forming Array With Three Criteria (Pass, Fail, Blank Cells)

[Zedrick13]

This formula doesn't seem to work:

=IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)="","Unexecuted",IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)="Fail","Fail",IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)=OR("Pass",""))))

Does anyone have any idea how to fix it? The intention is:
1. If columns Q;S;U;W;Y;AA have at least 1 Fail, value for column O must be "Fail".
2. If columns Q;S;U;W;Y;AA are a mix of Pass and blanks only, it's a "Pass" for column O.
3. If columns Q;S;U;W;Y;AA are all blank, column O returns value "Unexecuted".

I used the CHOOSE function to create an array for the non-adjacent cells.

 

[AlanY]

check this out

=IF(ISERROR(MATCH("Fail",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33),0)>0),IF(ISERROR(MATCH("Pass",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33),0)>0),"Unexecuted","Pass"),"Fail")

 

[Zedrick13]

You're a genius!!!!!!!! It worked perfectly. Thank you sooooooooooooooooo much.

 

[AlanY]

you're welcome

 

[Zedrick13]

Hi @AlanY,

Can you please please please help me one more time?

 

[Zedrick13]

Hi again @AlanY,

Really sorry to bother you. You were a huge help to me. I realized one more condition that should apply to my formula problem before. I tried to work on it on my own using the formula that you gave me when another condition came up (something I haven't thought of previously before requesting your help). I've been wracking my brain all afternoon but I can't seem to apply the additional condition that came up. I really really hope you can help me.

So, here is the change that I am planning to apply to the formula that you gave me:

1. If columns Q;S;U;W;Y;AA have at least 1 Fail, value for column O must be "Fail".
2. If columns Q;S;U;W;Y;AA are a mix of Pass and blanks only, it's a "WIP" for column O.
3. If columns Q;S;U;W;Y;AA are all blank, column O returns value "Unexecuted".
4. If columns Q;S;U;W;Y;AA are all Pass, column O should show "Pass".

Thank you in advance!

 

[AlanY]

try this

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Fail",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)))))>0,"Fail",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)))))=5,"Pass",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)))))>0,"WIP","Unexecuted")))

 

[Zedrick13]

This is perfect! This really helped me a lot @AlanY. You don't know how much this means to me. So, thank you, really.

try this

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Fail",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)))))>0,"Fail",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)))))=5,"Pass",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)))))>0,"WIP","Unexecuted")))

 

[AlanY]

that's ok.
try not to duplicate questions in the forum or the mod will all over you.

please put a note on the duplicate question

 

[Zedrick13]

Hi, @AlanY. It might have a little bit of a problem.