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

Zedrick13

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

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
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
94
You're a genius!!!!!!!! It worked perfectly. Thank you sooooooooooooooooo much.
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
94

ADVERTISEMENT

Hi @AlanY,

Can you please please please help me one more time?
 

Zedrick13

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

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,378
Members
414,234
Latest member
grlevesq

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