AND/OR function with blank cells

olympiac

Board Regular
Joined
Sep 26, 2010
Messages
158
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
 
Here is the scenario:

<TABLE cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>Cell A1</TD><TD class=xl23 style="BORDER-LEFT: medium none; WIDTH: 48pt" align=middle width=64>TRUE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A2</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">no</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A3</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">no</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A4</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">no</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A5</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">no</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A6</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A7</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">no</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A8</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Cell A9</TD><TD class=xl22 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR></TBODY></TABLE>etc

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

=COUNTIF(A2:A60,"<>no")-COUNTBLANK(A2:A60)=0
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Or try this (array formula - use Ctrl+Shift+Enter and not only Enter):

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

Markmzz
The double unary is not needed in that application.

=SUM((A2:A60="no")+(A2:A60=""))-ROWS(A2:A60)=0
 
Upvote 0
Or try this (array formula - use Ctrl+Shift+Enter and not only Enter):

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

Markmzz
<!-- / message -->
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,707
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top