If functions in an array

[paul_willie78]

Hi folks

I have a working set of If functions in an array formula. I was wondering if anyone had a neater way of doing this.

{=IF(IF(OR(K3="A",K3="B",K3="C",K3="A*"),1,0)+IF(OR(L3="A",L3="B",L3="C",L3="A*"),1,0)+IF(OR(M3="A",M3="B",M3="C",M3="A*"),1,0)+IF(OR(O3="A",O3="B",O3="C",O3="A*"),1,0)+IF(OR(U3="A",U3="B",U3="C",U3="A*"),1,0)+IF(OR(W3="A",W3="B",W3="C",W3="A*"),1,0)>=5,TRUE,FALSE)}

if a cell is a A,B,C or A* then then set a value of 1
in this instance i'm only looking at 6 non adjacent cells
if they equate to 5 or more i would ilke a true else false.

Thanks in advance

Paul

 

[BobUmlas]

1 - you don't need this to be array-entered;
2 - when you add true + true, it works as 1+1, so you don't need the interior IF's. This is somewhat shorter:

=IF(OR(K3="A",K3="B",K3="C",K3="A*")+OR(L3="A",L3="B",L3="C",L3="A*")+OR(M3="A",M3="B",M3="C",M3="A*")+OR(O3="A",O3="B",O3="C",O3="A*")+OR(U3="A",U3="B",U3="C",U3="A*")+OR(W3="A",W3="B",W3="C",W3="A*")>=5,TRUE,FALSE)

 

[paul_willie78]

Thanks for that

you are right thats much shorter.

you know what it's like when you stare at something for long enough

 

[Haseeb Avarakkan]

Hello, Also Try

=SUM(COUNTIF(K3:M3,{"A*","B","C"}),COUNTIF(U3,{"A*","B","C"}),COUNTIF(W3,{"A*","B","C"}))>=5