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.

 

[Zedrick13]

Sorry, @AlanY. I will make sure not to do it again (duplicating questions). The formula might have a little bit of a problem. I wonder if you could take a look at cell O292? The value at O292 is WIP but the cells I defined as array were all Pass.

https://drive.google.com/open?id=1h7KSZ2TIqP8SE0LlmMeKPM9hFrkhn1Hh

I also tried to put all Pass for all six cells defined in the array, but the value at cell O return WIP.

 

[AlanY]

because on that particular row you're only testing 2 cells, S922 & Y292 instead of 5; you need to change that part of formula to

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Fail",CHOOSE({1;2},S292,Y292)))))>0,"Fail",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2},S292,Y292)))))=[COLOR="#FF0000"]2[/COLOR],"Pass",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2},S292,Y292)))))>0,"WIP","Unexecuted")))

 

[Zedrick13]

I see. That's brilliant. So, the formula should be different depending on the number of the values in the array. Anyway, I hope I am not imposing on you. So, a tried a couple of scenarios when there are 6 values in the array. And, I seem to be having issues with the formula at rows 57 to 61. Hope you can take another look.

https://drive.google.com/open?id=1h7KSZ2TIqP8SE0LlmMeKPM9hFrkhn1Hh

 

[Zedrick13]

Sorry about the question above. I tried checking the formula again. It turns out I just have to change the number after the array. I'll keep checking.

 

[AlanY]

actually that was my mistake, to check on 6 cells, that value should be 6 anyway.
don't know why I put 5 there in the first place

 

[Zedrick13]

Thank you again @AlanY. Yeah, I changed it already. Everything's perfect now. You're such a huge help. Thanks.