Using Count function with a conditional range
 

I have a spreadsheet with dates in column A and a true/false in column B. I
want to count how many "false" are in column B between certain dates. If I
specify the cells where the date range starts and finishes, this works OK
e.g. =COUNT(A241:A265,0) What I want to do is replace A:241:A265 by an
expression that selects the cells in column A that lie between two dates.

Any ideas?

Thanks
Phil

Hi Phil

One way

You can use sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Try something like this(Change range)

=SUMPRODUCT((A1:A10=DATE(2003,1,12))*((A1:A10<=DA TE(2003,7,18)))*((B1:B10=FALSE)))


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Phil" <philDOTtoddATbluewinDOTch wrote in message ...
I have a spreadsheet with dates in column A and a true/false in column B. I
want to count how many "false" are in column B between certain dates. If I
specify the cells where the date range starts and finishes, this works OK
e.g. =COUNT(A241:A265,0) What I want to do is replace A:241:A265 by an
expression that selects the cells in column A that lie between two dates.

Any ideas?

Thanks
Phil

Hi!

Try this:

Enter your date boundaries in 2 cells:

C1 = lower date boundary
D1 = upper date boundary

=SUMPRODUCT(--(A1:A365=C1),--(A1:A365<=D1),--(B1:B365="False"))

If the true/false in column B are really Boolean values then leave out the
quotes.

Biff

"Phil" <philDOTtoddATbluewinDOTch wrote in message
...
I have a spreadsheet with dates in column A and a true/false in column B. I
want to count how many "false" are in column B between certain dates. If I
specify the cells where the date range starts and finishes, this works OK
e.g. =COUNT(A241:A265,0) What I want to do is replace A:241:A265 by an
expression that selects the cells in column A that lie between two dates.

Any ideas?

Thanks
Phil

Thanks Biff,

This does exactly what I was trying to do.

Phil
"Biff" wrote in message
...
Hi!

Try this:

Enter your date boundaries in 2 cells:

C1 = lower date boundary
D1 = upper date boundary

=SUMPRODUCT(--(A1:A365=C1),--(A1:A365<=D1),--(B1:B365="False"))

If the true/false in column B are really Boolean values then leave out the
quotes.

Biff

"Phil" <philDOTtoddATbluewinDOTch wrote in message
...
I have a spreadsheet with dates in column A and a true/false in column B.
I
want to count how many "false" are in column B between certain dates. If
I
specify the cells where the date range starts and finishes, this works OK
e.g. =COUNT(A241:A265,0) What I want to do is replace A:241:A265 by an
expression that selects the cells in column A that lie between two dates.

Any ideas?

Thanks
Phil