Count the occurances of a month in a range of date fields

I am needing to count the number of times each month is repeated in a column.
The column is a date field in MM/DD/YY format. I attempted to change the
format to MMMM DD, YYYY format (e.g. January 1, 2005) and count off of the
word January, but that did not work (=COUNTIF(C:C,"*January*")).

Is there way to find out how many times a month is repeated?

Thank you

One way

=SUMPRODUCT(--(ISNUMBER(C1:C1000)),--(MONTH(C1:C1000)=1))

note that you cannot use this formula with C:C, you need to specify
something like C1:C1000

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Keith Brown" <KeithBrown@discussions.microsoft.com> wrote in message
news:908117EB-5F18-491E-BCCB-A3A769A1545E@microsoft.com...
>I am needing to count the number of times each month is repeated in a
>column.
> The column is a date field in MM/DD/YY format. I attempted to change the
> format to MMMM DD, YYYY format (e.g. January 1, 2005) and count off of the
> word January, but that did not work (=COUNTIF(C:C,"*January*")).
>
> Is there way to find out how many times a month is repeated?
>
> Thank you

Try:

=SUMPRODUCT(--(TEXT(C1:C30,"mmm")="Jan"))

HTH
Jason
Atlanta, GA

"Keith Brown" wrote:

> I am needing to count the number of times each month is repeated in a column.
> The column is a date field in MM/DD/YY format. I attempted to change the
> format to MMMM DD, YYYY format (e.g. January 1, 2005) and count off of the
> word January, but that did not work (=COUNTIF(C:C,"*January*")).
>
> Is there way to find out how many times a month is repeated?
>
> Thank you

For the first response I modified it to =SUMPRODUCT(--(MONTH($H$6:$H$155)=1))
and I am getting a #VALUE!

For the second response I modified it to
=SUMPRODUCT(--(MONTH($H$6:$H$155)=1)) and I am getting 37 but I only have two
dates in that column with a month of January.

Any other ideas?



"Keith Brown" wrote:

> I am needing to count the number of times each month is repeated in a column.
> The column is a date field in MM/DD/YY format. I attempted to change the
> format to MMMM DD, YYYY format (e.g. January 1, 2005) and count off of the
> word January, but that did not work (=COUNTIF(C:C,"*January*")).
>
> Is there way to find out how many times a month is repeated?
>
> Thank you

1. You have text and not excel dates or you have mixed values where some of
your values are text
2. You probably have blank cells which will be counted as January, try this
version of Jason's formula

=SUMPRODUCT(--(TEXT(H6:H155,"mmm")="Jan"),--(H6:H155<>""))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Keith Brown" <KeithBrown@discussions.microsoft.com> wrote in message
news:075BCF9B-FF3E-4CF4-ACEC-7DE20E71247D@microsoft.com...
> For the first response I modified it to
> =SUMPRODUCT(--(MONTH($H$6:$H$155)=1))
> and I am getting a #VALUE!
>
> For the second response I modified it to
> =SUMPRODUCT(--(MONTH($H$6:$H$155)=1)) and I am getting 37 but I only have
> two
> dates in that column with a month of January.
>
> Any other ideas?
>
>
>
> "Keith Brown" wrote:
>
>> I am needing to count the number of times each month is repeated in a
>> column.
>> The column is a date field in MM/DD/YY format. I attempted to change
>> the
>> format to MMMM DD, YYYY format (e.g. January 1, 2005) and count off of
>> the
>> word January, but that did not work (=COUNTIF(C:C,"*January*")).
>>
>> Is there way to find out how many times a month is repeated?
>>
>> Thank you

=SUMPRODUCT(--(DATE(YEAR($H$6:$H$155),MONTH($H$6:$H$155),1)=J2))

where J2 houses the criterion mont/year, that is, a true date, set to
show the first of the mont/year, like 1-Jan-2005 or a formula like:

=DATE(2005,1,1)

Keith Brown wrote:
> For the first response I modified it to =SUMPRODUCT(--(MONTH($H$6:$H$155)=1))
> and I am getting a #VALUE!
>
> For the second response I modified it to
> =SUMPRODUCT(--(MONTH($H$6:$H$155)=1)) and I am getting 37 but I only have two
> dates in that column with a month of January.
>
> Any other ideas?
>
>
>
> "Keith Brown" wrote:
>
>
>>I am needing to count the number of times each month is repeated in a column.
>> The column is a date field in MM/DD/YY format. I attempted to change the
>>format to MMMM DD, YYYY format (e.g. January 1, 2005) and count off of the
>>word January, but that did not work (=COUNTIF(C:C,"*January*")).
>>
>>Is there way to find out how many times a month is repeated?
>>
>>Thank you

Peo - thank you that worked wonderful.

"Peo Sjoblom" wrote:

> 1. You have text and not excel dates or you have mixed values where some of
> your values are text
> 2. You probably have blank cells which will be counted as January, try this
> version of Jason's formula
>
> =SUMPRODUCT(--(TEXT(H6:H155,"mmm")="Jan"),--(H6:H155<>""))
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please, for everyone's
> benefit keep the discussion in the newsgroup/forum)
>
>
>
> "Keith Brown" <KeithBrown@discussions.microsoft.com> wrote in message
> news:075BCF9B-FF3E-4CF4-ACEC-7DE20E71247D@microsoft.com...
> > For the first response I modified it to
> > =SUMPRODUCT(--(MONTH($H$6:$H$155)=1))
> > and I am getting a #VALUE!
> >
> > For the second response I modified it to
> > =SUMPRODUCT(--(MONTH($H$6:$H$155)=1)) and I am getting 37 but I only have
> > two
> > dates in that column with a month of January.
> >
> > Any other ideas?
> >
> >
> >
> > "Keith Brown" wrote:
> >
> >> I am needing to count the number of times each month is repeated in a
> >> column.
> >> The column is a date field in MM/DD/YY format. I attempted to change
> >> the
> >> format to MMMM DD, YYYY format (e.g. January 1, 2005) and count off of
> >> the
> >> word January, but that did not work (=COUNTIF(C:C,"*January*")).
> >>
> >> Is there way to find out how many times a month is repeated?
> >>
> >> Thank you
>
>
>

Hello,

I tried this solution:

=SUMPRODUCT(--(ISNUMBER(A2:A1000)),--(MONTH(A2:A1000)=2))

I got #Value! in the cell. When I take the --(MONTH(A2:A1000)=2)) away
the formula works but counts all cells. What am I doing wrong?

On Mon, 14 Mar 2005 00:31:49 -0600, mijoh20@hotmail-dot-com.no-spam.invalid
(mijoh20) wrote:

>Hello,
>
>I tried this solution:
>
>=SUMPRODUCT(--(ISNUMBER(A2:A1000)),--(MONTH(A2:A1000)=2))
>
>I got #Value! in the cell. When I take the --(MONTH(A2:A1000)=2)) away
>the formula works but counts all cells. What am I doing wrong?

If there is non-numeric values in A2:A1000, your MONTH function will still
evaluate to #VALUE!. So your formula would evaluate:

=SUMPRODUCT(--(FALSE),--(#VALUE!))
=#VALUE!

Try the *array* formula:

=SUM(IF(ISNUMBER(A1:A1000),--(MONTH(A1:A1000)=2)))

To enter an *array* formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron