Get count of records for a particular month and year

M

maxtrixx

I have a column with date records and I need to find out how many
records/month/year. can anyone help me? Thanks.
 
B

Bernie Deitrick

Select your column of date records, then use Data | Pivot table. Click
through to the end, then drag the date field button to both the row field
and the data field. Then use the Pivot Table button on the pivot table
commandbar, and choose group. Then select month or year, and you will get a
count of dates within each time period.

HTH,
Bernie
MS Excel MVP
 
J

JulieD

Hi

i would suggest using a pivot table for this ... click in your data area
choose data / pivot table & pivot chart report
choose next
ensure the correct data range is selected
choose next
click on layout
drag the date field to where it says "row"
drag the records field to where it says "data" and if it says "sum of ...."
double click on it and change it to count
click OK
click finish
now right mouse click on the dates area, choose group & show detail / group
click on month & year and click OK
 
F

Fredrik Wahlgren

maxtrixx said:
I have a column with date records and I need to find out how many
records/month/year. can anyone help me? Thanks.
Click to expand...

I will assume your dates are in column A, row 1 to 10
1) In column B, enter =YEAR(A1) and drag downwards
2) In column C, enter =MONTH(A1)and drag downwards

Now, lets say you want how many dates you have for January 2005
Use this formula:

=SUMPRODUCT((B1:B10=2005)*(C1:C10=1))

/Fredrik
 
J

Jonathan Cooper

assume your data range is from A1:A100

In B1 type =min(a1:a100) this will tell you the earliest date.
In B2 type =max(A1:A100) tells you latest date.

Now from B3 downward until you've covered the min and max dates, type (or
autofill or by formula) a date for every month that needs to be counted.

Starting in C3,
=SUMPRODUCT((A1:A100>=DATE(YEAR(B3),MONTH(B3),1))*(A1:A100<=DATE(YEAR(B3),MONTH(B2)+1,0)))

copy/paste this formula down next to each date in B3 the bottom of your list.

And, if you need a formula to fill down the list

in B3 =min(A1:A100)
in B4 =date(year(b3),month(b3)+1,day(1)) copy/paste this down

must give credit to http://www.beyondtechnology.com/tips100.shtml for the
way to find beginning of and end of month dates w/o using eomonth()
 
J

Jonathan Cooper

Correction:
formula in C3 is:

=SUMPRODUCT(($A$1:$A$100>=DATE(YEAR(B3),MONTH(B3),1))*($A$1:$A$100<=DATE(YEAR(B3),MONTH(B3)+1,0)))
 
Top