Counting the number of MONTH in a column

[Broomer]

Hi, just wondering how I can count the number of times a month occurs
within a column.
Example

Date
=====
8/9/06
8/9/06
8/9/06
9/10/06
9/10/06
9/10/06
8/9/06
8/9/06


Answer:
8/9/06 = 5
9/10/06 = 3

Thanks. Done it before but can't remember how the heck I did it. I
blame alcohol.

Kind regards,

Ka Kwok

 

[Dave Peterson]

Do you actually mean dates?
=countif(a1:A10,date(2006,8,9))
(assuming 8/9/06 represents August 9th, 2006)

If you meant the month in any year:
=SUMPRODUCT(--(TEXT(A1:A10,"mm")="08"),--(A1:A10<>""))

If you meant a month of a given year:
=sumproduct(--(text(a1:a10,"yyyymm")="200608")



if yo

 

[sfb]

What about the MONTH function? If it is formatted true date as serial number
and not a text string, then MONTH eliminates any dependencies on the format
of the data.

 

[Bob Phillips]

I would create a helper column with the month number, so in B2

=MONTH(A2)

then count them with

=SUMPRODUCT((B2:B20<>"")/COUNTIF(B2:B200,B2:B20&""))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

I think that was exactly what I gave you.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Broomer]

Dave, Bob, SFB - Thanks.
Getting closer.

What I am trying to do is to count the number of times a month occurs
within the array. It's basically for a worksheet that I've got that
records the number of times the software I'm using falls over.

So each time the thing falls on it's head, I just grab a date/time
stamp and copy it into a column.

Now I want to conduct a count on the number of times the thing has
tripped with each month. Later on, I'm going to grab a mean and a
standard deviation.

I've done this before but right now it's not coming to me how I did it.

Thanks again,

Kind regards,

Ka Kwok

 

[Broomer]

Thanks Champ.

Sorted.
As my English teacher once said: KISS.

Kind regards,

Ka Kwok