If function nested three times

[Nick70]

Hi,

We have text in cells C:4, E:4 and G:4.

Possible text in cell C:4 is equal to Yes / Maybe/ No (so three possible answers).
Possible text in cell E:4 is equal to Yes / Maybe/ No (so three possible answers).
Possible text in cell G:4 is equal to Yes / No (so two possible answers).

I would like to have a formula in cell H:4 that gives back text "Pass" if there is at least one "Yes" or one "Maybe" in either cell C:4 or in cell E:4 (so for example "Yes" in cell C:4 and text "No" in cell E:4) and text "Yes" in cell G:4.

Formula in cell H:4 will give back text "Fail" if there is at least one "Yes" or one "Maybe" in either cell C:4 or cell E:4 (so for example "No" in cell C:4 and text "Maybe" in cell E:4).and text "No" in cell G:4.

Formula in cell H:4 will give back text "Repeat" if cell C:4 and cell E:4 have both text "No" and cell G:4 has text "No".

Formula in cell H:4 will give back text "Continue" if cell C:4 and cell E:4 have both text "No" and cell G:4 has text "Yes".

How do I do this?

Thanks,
Nic

 

[Cubist]

Try:

=SWITCH(G4,"No",IF(COUNTIF(C4:D4,"No")=2,"Repeat","Fail"),"Yes",IF(COUNTIF(C4:D4,"No")=2,"Continue","Pass"))

 

[Nick70]

Great thanks,
N.

 

[Cubist]

I've mistaken column D for E. As long as column D doesn't have one of the listed values "Yes", "No", "Maybe" you can extend the range to C4:E4.