COUNTIFS IN EXCEL 2007: Text & Numeric Values

[jimmy2times]

in cell O45 I have a countif formula


The formula is looking at the cell range O7:O11 and counting yes,no, n/a appear.


I then want to conver the result of the countif from a number to a text value using this logic:


If the "no" count is zero, return "yes"


If the "no" count is 1-5, return "no"


So I think it will be something like an if statement combined with a countif & then converting number to text.


If there is a way of achieving this by formula or by VBA please let me know

 

[ghrain22]

=if(countif($O$7:$O$11,"=no")>0,"no","yes")

 

[njimack]

Another approach:
=LOOKUP(COUNTIF($O$7:$O$11,"no"),{0,5},{"yes","no"})

 

[jimmy2times]

Another approach:
=LOOKUP(COUNTIF($O$7:$O$11,"no"),{0,5},{"yes","no"})

Hi Neil,

Thanks for this. It is a step in the right direction. When I test the formula, it is still not quite right. If some of the cells in O7:O11 are NO and some are Yes, the formula should return NO but it is still showing Yes.

Can you tweak this formula to rectify?

 

[njimack]

Hi Neil,

Thanks for this. It is a step in the right direction. When I test the formula, it is still not quite right. If some of the cells in O7:O11 are NO and some are Yes, the formula should return NO but it is still showing Yes.

Can you tweak this formula to rectify?

My bad - change the 5 to a 1 and you should be good to go.

 

[jimmy2times]

My bad - change the 5 to a 1 and you should be good to go.

Cheers mate. Works a treat