Help on an array formula or other solution - non VBA

[philwojo]

Hello, I am looking for assistance on why this array formula isn't working as I expect, I am new to using array's. I have drop down selections i K37:k43, they can be one of the following:

- Select One
- N/A
- 0
- 1

Here is the formula I have:

{=IFERROR(IF((IF((K37:K43)=1,1,0))/(IF(ISNUMBER(K37:K43),1,0))=1,"Pass","Fail"),"N/A")}

The way it is working now if I put a 1 into K37 it gives me "PASS", if I put a 0 it gives me "FAIL", regardless of what the other cells contain. If I put in Select One or N/A in K37 then I get "N/A" back.

What I want this formula to do is to say that if all of the values from K37:K43 are a 1, return "PASS". If any of them are a 0 (Zero) return "Fail", if any of them contain "Select One" or "N/A" in them, then give me "N/A" back.

What do I have to do to fix my code. I am trying to adapt someone else code but I'm just not grasping what is going on with it right now, again new to arrays.

Thanks,
Phil

 

[Marcelo Branco]

Maybe...

=IF(COUNTIF(K37:K43,0),"Fail",IF(SUM(COUNTIF(K37:K43,{"Select One";"N/A"})),"N/A","Pass"))

M.

 

[philwojo]

Thanks Marcelo, I think this is working how I expect, I have to do a bit more testing, and I also have to understand how other people expect a few things to work based on possible combinations. But I appreciate the assistance.