count date occurances in range of dates...
 

Hi

I am stuck trying to come up with a formula to count the number of times
dates occur within a range of dates.
What I have is:
col A has a range of dates, say A1:A100
col b with another set of dates B1:B7
What I need to do is return the number of times any of the individual dates
in the B1:B7 range occur in A1:A100.
e.g
A B
10 Feb 05 26 Mar 05
15 Feb 05 29 Apr 05
26 Mar 05 10 Feb 05
29 Apr 05
06 May 05
11 Jan 05
18 Dec 05

The answer I'm looking for here would be 3. Each date in col B occurs once
in col A.

Getting very frustrated with this and can't seem to find an answer for it!!
Many thanks in advance

Alex

=SUMPRODUCT(COUNTIF(A1:A7,B1:B3))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alex" wrote in message
...
Hi

I am stuck trying to come up with a formula to count the number of times
dates occur within a range of dates.
What I have is:
col A has a range of dates, say A1:A100
col b with another set of dates B1:B7
What I need to do is return the number of times any of the individual
dates
in the B1:B7 range occur in A1:A100.
e.g
A B
10 Feb 05 26 Mar 05
15 Feb 05 29 Apr 05
26 Mar 05 10 Feb 05
29 Apr 05
06 May 05
11 Jan 05
18 Dec 05

The answer I'm looking for here would be 3. Each date in col B occurs once
in col A.

Getting very frustrated with this and can't seem to find an answer for
it!!
Many thanks in advance

Alex

Thanks for the speedy answer. Works great.

Cheers,
Alex.


"Bob Phillips" wrote:

=SUMPRODUCT(COUNTIF(A1:A7,B1:B3))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alex" wrote in message
...
Hi

I am stuck trying to come up with a formula to count the number of times
dates occur within a range of dates.
What I have is:
col A has a range of dates, say A1:A100
col b with another set of dates B1:B7
What I need to do is return the number of times any of the individual
dates
in the B1:B7 range occur in A1:A100.
e.g
A B
10 Feb 05 26 Mar 05
15 Feb 05 29 Apr 05
26 Mar 05 10 Feb 05
29 Apr 05
06 May 05
11 Jan 05
18 Dec 05

The answer I'm looking for here would be 3. Each date in col B occurs once
in col A.

Getting very frustrated with this and can't seem to find an answer for
it!!
Many thanks in advance

Alex