I have a column of dates such as 8/5/06, I would like to create a formula
that counts the number of dates for a particular month. For example, if the
column reads as such:
9/13/06
8/28/06
9/20/06
9/20/06
8/20/06
9/22/06
9/22/06

I would like the formula to tell me that there are 2 for the month of
August. Then I could also set up another similar formular to tell me that
there are 5 for the month of September


--
Thanks,
Marc Shaw

Something like this might be what you're looking for.........

=COUNTIF(A:A,">=9/1/06")-COUNTIF(A:A,">=10/1/06")

Vaya con Dios,
Chuck, CABGx3



"Marc Shaw" wrote:

> I have a column of dates such as 8/5/06, I would like to create a formula
> that counts the number of dates for a particular month. For example, if the
> column reads as such:
> 9/13/06
> 8/28/06
> 9/20/06
> 9/20/06
> 8/20/06
> 9/22/06
> 9/22/06
>
> I would like the formula to tell me that there are 2 for the month of
> August. Then I could also set up another similar formular to tell me that
> there are 5 for the month of September
>
>
> --
> Thanks,
> Marc Shaw

Marc

one way:

=SUMPRODUCT( --(G1:G7>=DATE(2006,8,1)),-- (G1:G7<=DATE(2006,8,31)) )
=SUMPRODUCT( --(G1:G7>=DATE(2006,9,1)),-- (G1:G7<=DATE(2006,9,30)) )

assuming the dates are in cells G1 to G7

Regards

Trevor

"Marc Shaw" <(E-Mail Removed)> wrote in message
news:3A1FC743-8A14-4FD3-B383-(E-Mail Removed)...
>I have a column of dates such as 8/5/06, I would like to create a formula
> that counts the number of dates for a particular month. For example, if
> the
> column reads as such:
> 9/13/06
> 8/28/06
> 9/20/06
> 9/20/06
> 8/20/06
> 9/22/06
> 9/22/06
>
> I would like the formula to tell me that there are 2 for the month of
> August. Then I could also set up another similar formular to tell me that
> there are 5 for the month of September
>
>
> --
> Thanks,
> Marc Shaw

One way:

=SUMPRODUCT(--(MONTH(B1:B20)=8))

=SUMPRODUCT(--(MONTH(B1:B20)=9))

HTH

"Marc Shaw" wrote:

> I have a column of dates such as 8/5/06, I would like to create a formula
> that counts the number of dates for a particular month. For example, if the
> column reads as such:
> 9/13/06
> 8/28/06
> 9/20/06
> 9/20/06
> 8/20/06
> 9/22/06
> 9/22/06
>
> I would like the formula to tell me that there are 2 for the month of
> August. Then I could also set up another similar formular to tell me that
> there are 5 for the month of September
>
>
> --
> Thanks,
> Marc Shaw

Here's one way to get all 12 months (assuming the year is the same):

Assuming your data is in the range A1:A7.

Enter this formula for the month name in say, E1:

=TEXT(DATE(2006,ROWS($1:1),1),"mmmm")

Enter this formula for the count in F1:

=SUMPRODUCT(--(MONTH(A$1:A$7)=ROWS($1:1)))

Select both E1 and F1 then copy down to row 12.

Biff

"Marc Shaw" <(E-Mail Removed)> wrote in message
news:3A1FC743-8A14-4FD3-B383-(E-Mail Removed)...
>I have a column of dates such as 8/5/06, I would like to create a formula
> that counts the number of dates for a particular month. For example, if
> the
> column reads as such:
> 9/13/06
> 8/28/06
> 9/20/06
> 9/20/06
> 8/20/06
> 9/22/06
> 9/22/06
>
> I would like the formula to tell me that there are 2 for the month of
> August. Then I could also set up another similar formular to tell me that
> there are 5 for the month of September
>
>
> --
> Thanks,
> Marc Shaw

Marc,

=SUMPRODUCT(--(MONTH(A1:A1000)=8))
=SUMPRODUCT(--(MONTH(A1:A1000)=9))

HTH,
Bernie
MS Excel MVP


"Marc Shaw" <(E-Mail Removed)> wrote in message
news:3A1FC743-8A14-4FD3-B383-(E-Mail Removed)...
>I have a column of dates such as 8/5/06, I would like to create a formula
> that counts the number of dates for a particular month. For example, if the
> column reads as such:
> 9/13/06
> 8/28/06
> 9/20/06
> 9/20/06
> 8/20/06
> 9/22/06
> 9/22/06
>
> I would like the formula to tell me that there are 2 for the month of
> August. Then I could also set up another similar formular to tell me that
> there are 5 for the month of September
>
>
> --
> Thanks,
> Marc Shaw

Thank you Chuck, I knew it should be an easy formula but the brain just
wasn't clicking.

Thanks again!
--
Thanks,
Marc Shaw


"CLR" wrote:

> Something like this might be what you're looking for.........
>
> =COUNTIF(A:A,">=9/1/06")-COUNTIF(A:A,">=10/1/06")
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Marc Shaw" wrote:
>
> > I have a column of dates such as 8/5/06, I would like to create a formula
> > that counts the number of dates for a particular month. For example, if the
> > column reads as such:
> > 9/13/06
> > 8/28/06
> > 9/20/06
> > 9/20/06
> > 8/20/06
> > 9/22/06
> > 9/22/06
> >
> > I would like the formula to tell me that there are 2 for the month of
> > August. Then I could also set up another similar formular to tell me that
> > there are 5 for the month of September
> >
> >
> > --
> > Thanks,
> > Marc Shaw

Happy to help, Marc...........thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3



"Marc Shaw" wrote:

> Thank you Chuck, I knew it should be an easy formula but the brain just
> wasn't clicking.
>
> Thanks again!
> --
> Thanks,
> Marc Shaw
>
>
> "CLR" wrote:
>
> > Something like this might be what you're looking for.........
> >
> > =COUNTIF(A:A,">=9/1/06")-COUNTIF(A:A,">=10/1/06")
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Marc Shaw" wrote:
> >
> > > I have a column of dates such as 8/5/06, I would like to create a formula
> > > that counts the number of dates for a particular month. For example, if the
> > > column reads as such:
> > > 9/13/06
> > > 8/28/06
> > > 9/20/06
> > > 9/20/06
> > > 8/20/06
> > > 9/22/06
> > > 9/22/06
> > >
> > > I would like the formula to tell me that there are 2 for the month of
> > > August. Then I could also set up another similar formular to tell me that
> > > there are 5 for the month of September
> > >
> > >
> > > --
> > > Thanks,
> > > Marc Shaw