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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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")
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
You're a genius!!!!!!!! It worked perfectly. Thank you sooooooooooooooooo much.
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100

ADVERTISEMENT

Hi @AlanY,

Can you please please please help me one more time?
 

Zedrick13

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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")))
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
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")))
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,461
Messages
5,831,778
Members
430,089
Latest member
Jane434

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
Top