AND/OR function with blank cells

[olympiac]

Hi,

I need a function that returns in cell A1 "TRUE" if the values in the range A2:A60 are "No" or Blank cell or a mix of both.
If there are cells that contain another value, the function will return "FALSE".

I think that I am supposed to use the AND function. Do I need to combine it with a OR Function?

Can you help please?

Thanks

 

[T. Valko]

Hi,

I need a function that returns in cell A1 "TRUE" if the values in the range A2:A60 are "No" or Blank cell or a mix of both.
If there are cells that contain another value, the function will return "FALSE".

I think that I am supposed to use the AND function. Do I need to combine it with a OR Function?

Can you help please?

Thanks

Do you mean if EVERY cell in the range is either blank or contains No then return TRUE or do you mean if ANY cell in the range is either blank or contains No then return TRUE.

 

[Gerald Higgins]

Let's be clear, do you want the single cell A1 to hold the results of the analysis for the whole of A2:A60 ?
So, for example, if A20 contains "YES", then A1 should show FALSE ?

If that's what you want, maybe

=if((countif(a2:a60,"")+countif(a2:a60,"No"))=59,TRUE,FALSE)

 

[barry houdini]

This version would avoid you having to include a count of the range (59) in case that changes

=SUMPRODUCT(((A2:A60<>"No")+(A2:A60<>"")=2)+0)=0

 

[olympiac]

Here is the scenario:

<table cellspacing="0" cellpadding="0" border="0" width="128"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;width:48pt" height="17" width="64">Cell A1</td> <td class="xl23" style="border-left:none;width:48pt" align="center" width="64">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A2</td> <td class="xl22" style="border-top:none;border-left:none">no</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A3</td> <td class="xl22" style="border-top:none;border-left:none">no</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A4</td> <td class="xl22" style="border-top:none;border-left:none">no</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A5</td> <td class="xl22" style="border-top:none;border-left:none">no</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A6</td> <td class="xl22" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A7</td> <td class="xl22" style="border-top:none;border-left:none">no</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A8</td> <td class="xl22" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;border-top:none" height="17">Cell A9</td> <td class="xl22" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>etc

If cell A7 for instance contains the value "xxxx" then the function will return FALSE.

 

[Gerald Higgins]

So if I understand you correctly, my suggestion should do that, and I think Barry's probably will too.

 

[olympiac]

Thanks for your answers.
Both formulas work fine.

Cheers

 

[Gerald Higgins]

Great, thanks for letting us know.
Bear in mind Barry's point - my suggestion includes the number 59 because that's how many rows you said you had. If the number of rows is variable, you'll need to allow for that somehow, and Barry's suggestion does that automatically.

 

[markmzz]

Or try this (array formula - use Ctrl+Shift+Enter and not only Enter):

=SUM(--(A2:A60="no")+--(A2:A60=""))-ROWS(A2:A60)=0

Markmzz

 

[olympiac]

Yes, it works as well.

Thanks