count date occurances in range of dates...

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

--

HTH

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


"Alex" <Alex@discussions.microsoft.com> wrote in message
news:D895F8F8-6CE1-4ABB-8D67-C906BF0D959A@microsoft.com...
> 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" <Alex@discussions.microsoft.com> wrote in message
> news:D895F8F8-6CE1-4ABB-8D67-C906BF0D959A@microsoft.com...
> > 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" <Alex@discussions.microsoft.com> wrote in message
news:D895F8F8-6CE1-4ABB-8D67-C906BF0D959A@microsoft.com...
> 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" <Alex@discussions.microsoft.com> wrote in message
> news:D895F8F8-6CE1-4ABB-8D67-C906BF0D959A@microsoft.com...
> > 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
> >
>
>
>

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" <Alex@discussions.microsoft.com> wrote in message
news:D895F8F8-6CE1-4ABB-8D67-C906BF0D959A@microsoft.com...
> 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" <Alex@discussions.microsoft.com> wrote in message
> news:D895F8F8-6CE1-4ABB-8D67-C906BF0D959A@microsoft.com...
> > 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
> >
>
>
>