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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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?
 
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)
 
=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)
 
Peo - thank you that worked wonderful.

Peo Sjoblom said:
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)
Click to expand...
 
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)) awa
the formula works but counts all cells. What am I doing wrong
 
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?
Click to expand...

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
 
Back
Top