Using Count function with a conditional range

[Phil]

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

 

[Ron de Bruin]

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<=DATE(2003,7,18)))*((B1:B10=FALSE)))

 

[Biff]

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]

Thanks Biff,

This does exactly what I was trying to do.

Phil