Return a value based on several cell values

[peterderrington]

Morning,

I have a calculation that looks at the values of a range of cells in a row:

{=IF(ISNUMBER(SEARCH("Yes",AG2:AJ2)),"Yes","")}

This returns an answer of "Yes" if it discovers a "Yes" in any of the 4 cells and leaves it blank if it doesn't discover any.

I would also like it to return an answer of "No" if it finds all 4 cells with "No" in them.

Any ideas?

Thanks

 

[AlanY]

may be

=IF(COUNTIF(AG2:AJ2,"Yes")>0,"Yes",IF(COUNTIF(AG2:AJ2,"No")=4,"No",""))

 

[peterderrington]

You sir are a star!

Thank you for this, it works a treat.

 

[AlanY]

you're welcome