EXCEL - Filtering rows by multiple OR conditions-CodePudding

I have this problem I'm trying to solve in EXCEL, hopefully it's a straightforward one somebody can help with.

Essentially I Have 6 columns, which can be of values 'compliant' or 'missing'.

What I'd like to achieve is that in the 7th column, 'compliant' or 'non-compliant' is written, if the following conditions are met:

1 of column N,L,J is missing (i.e. max of 2 'missing') Or any of P,V,Z is missing (i.e. none can be 'missing')

I hope that makes sense. At the minute I've cobbled together this horrible formula, but I think I'm on the wrong track completely:

=IF(OR(N2="Missing",L2="Missing",J2="Missing"),"Non-compliant",""),IF(OR(P2="Missing",V2="Missing",Z2="Missing"),"Non-compliant","")

CodePudding user response：

I think the logic you have is close but I don’t think an if statement can check if less than two cells are equal to something. My approach would be to sum a few Boolean values by converting true to 1 and false to 0 with the INT function. So:

=if(or(sum(int(N2=“missing”),int(L2=“Missing),int(J2=“missing))>1,sum(int(P2=“missing”),int(V2=“missing”),int(Z2=“missing”))>0),”non-compliant”,””)

Hope this helps!

CodePudding user response：

=IF(AND(COUNTIF(J1:N1,"m")>1,OR(P1="m",V1="m",Z1="m")),"non-compliant","compliant")