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

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
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.
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
check this out

Code:
=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")
 
Upvote 0
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!
 
Last edited:
Upvote 0
try this

Code:
=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")))
 
Upvote 0
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

Code:
=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")))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top