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

#### Zedrick13

##### Board Regular
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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")``

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

you're welcome

Hi @AlanY,

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

Last edited:
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")))``

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

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

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

Replies
13
Views
282
Replies
16
Views
442
Replies
4
Views
159
Replies
4
Views
318
Replies
0
Views
191

1,203,596
Messages
6,056,195
Members
444,850
Latest member
dancasta7

### 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.

### Which adblocker are you using?

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

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