Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Count entries by months
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Count entries by months
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 --- "Steve2788" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Count entries by months
=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))) -- Biff Microsoft Excel MVP "Max" wrote in message ... 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 --- "Steve2788" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Delete duplicate entries from Excel 2003 | Excel Worksheet Functions | |||
Excel 2003: Autofilter with Multiple Entries | Excel Discussion (Misc queries) | |||
COUNT MONTHS | Excel Worksheet Functions | |||
YTD total (6 months w/o each record having 6 entries) | Excel Discussion (Misc queries) |