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(2005,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7))))

Any suggestions?

Thanks, Bruno

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

<[email protected]> wrote in message
news:[email protected]...
> 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(2005,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7))))
>
> Any suggestions?
>
> Thanks, Bruno
>