Issue with IF and AND Statement for Excel

Solution 1:

you need to bring an OR into it:

=IF(AND(Y4="Yes",S4="Yes",P4="Yes",M4="Yes",J4="Yes",G4="Yes",D4="Yes"(OR(V4="Yes", V4="")),"Yes", "No")

Solution 2:

Instead of multiple IF or other logical test, I would like to suggest one smart formula, solves the issue.

 =IF(OR(ISBLANK($Y$4), $Y$4<>"Yes"),"NO",IF(COUNTIF($D$4:$S$4,"=Yes")>0,"YES","NO"))

• This formula will first check whether Y4 is either BLANK or Not Equals to YES, then returns NO, in case Y4 has Yes then returns YES.

• Next check the second IF, and return YES, if any of cells from D4 to S4 has Yes.

• Returns NO, if all are either BLANK or has text other than Yes.

N.B.

• In case if you would like to check cells in random order then you need to go with Nested IF and Logical test.

• In that case you may go with this one:

  =IF(OR(ISBLANK($Y$4), $Y$4<>"Yes"),"NO",IF(OR(D4="Yes",J4="Yes",P4="Yes"),"YES","NO"))

• You may adjust cell references in the formula as needed.