Excel 2003 Count entries by months

[Steve2788]

I have a table of data, one column of which is a date (when the entry was
created).

I want to create a summary table, of how many entries for each calander
month for last few years. This table will then be broken down by other
factors (to be added to the formula).

Simple enough in 2007, but I don't know the formula syntax in 2003.

 

[Max]

Assuming real dates within A2:A100
In say, C2:
=SUMPRODUCT((TEXT(A$2:A$100,"mmm")=TEXT(DATE(2008,ROWS($1:1),1),"mmm"))*(A$2:A$100<>""))
returns the required counts of dates for Jan
Copy C2 down to C13 to return for the rest of the 12 months: Feb, ... Dec
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[T. Valko]

=TEXT(DATE(2008,ROWS($1:1),1),"mmm")

Another way to write that:

=TEXT(ROWS(C$2:C2)*30,"mmm")

Or, assuming the range contains nothing but dates (and possibly empty
cells):

=SUMPRODUCT(--(MONTH(A$2:A$20)=ROWS(C$2:C2)),--(A$2:A$20<>""))

If no empty cells:

=SUMPRODUCT(--(MONTH(A$2:A$20)=ROWS(C$2:C2)))