Counting cells base on a predetermined range
 

Hello All,

Here's the dilemma.

I have a set of dates in two columns. I can determine the data set
from the first column but I can't figure out how to count the data in
the second column based on the data set.
Here's an example;

date_rec date_paid
8/24/2005 9/14/2005
8/25/2005 10/5/2005
8/31/2005 9/14/2005
9/1/2005 10/26/2005
9/1/2005 10/19/2005
9/2/2005 9/14/2005
9/6/2005 10/26/2005
9/6/2005 9/21/2005
9/6/2005 10/5/2005
9/6/2005 10/5/2005
9/6/2005 10/5/2005
9/6/2005 9/14/2005
9/8/2005 10/5/2005
9/8/2005 9/21/2005
9/9/2005 9/21/2005
9/9/2005 9/28/2005
9/11/2005 10/26/2005
9/12/2005 9/28/2005
9/12/2005
9/12/2005 9/28/2005
9/12/2005 9/28/2005
9/13/2005
9/13/2005 9/28/2005
9/13/2005 9/28/2005
9/13/2005 9/21/2005
9/13/2005 10/5/2005
9/13/2005
9/13/2005 10/5/2005
9/13/2005 9/28/2005
9/13/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005


In an empty cell I have;
=COUNTIF(date_rec,"<="&DATE(2005,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7))
which gives me a 7 day date range. What I need to do from here is to
count the corresponding populated cells in the date_paid range. Here
is what I have tried but I just get a zero value;
=COUNTIF(date_paid,(COUNTIF(date_rec,"<="&DATE(200 5,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7))))

Any suggestions?

Thanks, Bruno

Counting cells base on a predetermined range
 

Hi!

Better to use cells to hold the date range:

A1 = 9/7/2005
B1 = 9/13/2005

=SUMPRODUCT(--(date_rec=A1),--(date_rec<=B1),--(date_paid<""))

Biff

wrote in message
oups.com...
Hello All,

Here's the dilemma.

I have a set of dates in two columns. I can determine the data set
from the first column but I can't figure out how to count the data in
the second column based on the data set.
Here's an example;

date_rec date_paid
8/24/2005 9/14/2005
8/25/2005 10/5/2005
8/31/2005 9/14/2005
9/1/2005 10/26/2005
9/1/2005 10/19/2005
9/2/2005 9/14/2005
9/6/2005 10/26/2005
9/6/2005 9/21/2005
9/6/2005 10/5/2005
9/6/2005 10/5/2005
9/6/2005 10/5/2005
9/6/2005 9/14/2005
9/8/2005 10/5/2005
9/8/2005 9/21/2005
9/9/2005 9/21/2005
9/9/2005 9/28/2005
9/11/2005 10/26/2005
9/12/2005 9/28/2005
9/12/2005
9/12/2005 9/28/2005
9/12/2005 9/28/2005
9/13/2005
9/13/2005 9/28/2005
9/13/2005 9/28/2005
9/13/2005 9/21/2005
9/13/2005 10/5/2005
9/13/2005
9/13/2005 10/5/2005
9/13/2005 9/28/2005
9/13/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005


In an empty cell I have;
=COUNTIF(date_rec,"<="&DATE(2005,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7))
which gives me a 7 day date range. What I need to do from here is to
count the corresponding populated cells in the date_paid range. Here
is what I have tried but I just get a zero value;
=COUNTIF(date_paid,(COUNTIF(date_rec,"<="&DATE(200 5,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7))))

Any suggestions?

Thanks, Bruno