Count number of times the month has appeared

Hi,

How do I calculate the no. of times a month has appeared in my spreadsheet?

Eg.

Memo Date
7-Jan-2013
14-Jan-2013
10-Jan-2013
15-Jan-2013
16-Jan-2013
16-Jan-2013
9-Jan-2013
21-Jan-2013
23-Jan-2013
25-Jan-2013
24-Jan-2013
28-Jan-2013
25-Jan-2013
28-Jan-2013
29-Jan-2013
23-Jan-2013
5-Feb-2013
6-Feb-2013
7-Feb-2013
7-Feb-2013
11-Feb-2013
8-Feb-2013


I would like to count how may times "January" appeared and put the answer in column A2 of sheet 2.
Tried using this formula, but it does not work...

=COUNT('2013'!$B$6:$B$1048576, "january")

Please help... thanks~

Try this (Using supplied Data, Assumed starting in Column A,Row 1):
In B1:
=SUMPRODUCT(--(TEXT($A$1:$A$22,"mmmm")="January"))

OR, if you can do without the actual name :
=SUMPRODUCT(--(MONTH($A$1:$A$22)=1))

Hope this helps

hi there again Rianne. first of all, COUNT only count numbers. to count with criteria, use COUNTIF.

secondly, dates are actually numbers. to verify, type in 13mar2013 in A1. Format cell to General. you will see that A1 is 41346 (meaning 41,346th day from 1 Jan 1900). so finding the text "January" among numbers wont do anything. you are using Excel 2007, so use COUNTIFS to do a date range
=COUNTIFS('2013'!$B$6:$B$1048576,">="&--("1jan2013"),'2013'!$B$6:$B$1048576,"<="&--("31jan2013"))

or in older versions:
=SUMPRODUCT(--(TEXT('2013'!$B$6:$B$1048576,"mmyy")="0113"))

thirdly, you don't have to range up to 1 million over rows if you don't have to? just more than you realistically need

Thank you Dredwolf for your suggestion. Appreciate a lot.

--

Hi Beneshiryo, nice to hear from you again, I have tried your formula and it worked now. I also now understand the use of "Count" function. I used to think it would apply to all counting needs.. :D
Thank you very much for your help.

Use another column (a dummy column) next to all your dates and enter =MONTH(A1) and then drag down. A1 is just an example for your first data point, whatever that is. This formula will give you a 1 for Jan, 2 for Feb, 3 for Mar, etc. Then you can do a countif formula on those values to determine how many times January appeared (or whatever month you wish):

=COUNTIF(your range,1)