counting date entries by month & year
 

I have a column with over 11(K) date entries. This column grows daily. I
need a formula that will total the number of entries by month & year
(disregarding the day).

A very simple way to accomplish this is to put in a "helper column"
consisting of:
= text( year( your_date_cell), "####") & " - " & text( month(
your_date_cell), "##")

Then just let Excel subtotal (Data / Subtotal) "Count" on changes in
the helper column. This assumes that the entries are arranged
chronologically in the column containing "your_date_cell".

Chris

Create a helper column and add the year there
=YEAR(A2)
and another with the month
=MONTH(A2)

and then create a pivot table for all of it. Probably simpler than trying to
build yourself.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Di" wrote in message
...
I have a column with over 11(K) date entries. This column grows daily. I
need a formula that will total the number of entries by month & year
(disregarding the day).

On Mon, 22 Aug 2005 08:36:52 -0700, "Di" wrote:

I have a column with over 11(K) date entries. This column grows daily. I
need a formula that will total the number of entries by month & year
(disregarding the day).

Column A = your list of date entries.

B1:Bn A list of dates being the first of each month of interest.

B1: 1 Jan 2000
B2: 1 Feb 2000
B3: 1 Mar 2000
etc.

If you put the above dates in B1 and B2; select B1&B2; place the cursor in the
lower right corner of the selection until it changes to a cross-hair, then
press the left mouse button and drag it down, it will increment automatically.

Copy down further into the future than you need.

Format the cells: Format/Cells/Number/Custom Type: mmm yyyy

C1: =COUNTIF(A:A,"="&B1)-COUNTIF(A:A,"="&B2)

Format as number.
Copy/Drag down as needed.




--ron

What is a helper column? The date column is not arranged chronologically &
is about 50 percent blank.
=MONTH(A2)
=YEAR(A2)
Just gives me the year & month entered in the respective cells but doesnt
count anything. Could I have the cells formatted incorrectly?



"Bob Phillips" wrote:

Create a helper column and add the year there
=YEAR(A2)
and another with the month
=MONTH(A2)

and then create a pivot table for all of it. Probably simpler than trying to
build yourself.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Di" wrote in message
...
I have a column with over 11(K) date entries. This column grows daily. I
need a formula that will total the number of entries by month & year
(disregarding the day).

The date column is not arranged chronologically & is about 50 percent blank.

"Blue Hornet" wrote:

A very simple way to accomplish this is to put in a "helper column"
consisting of:
= text( year( your_date_cell), "####") & " - " & text( month(
your_date_cell), "##")

Then just let Excel subtotal (Data / Subtotal) "Count" on changes in
the helper column. This assumes that the entries are arranged
chronologically in the column containing "your_date_cell".

Chris

A variation of this worked!! Thanks for the help!

"Ron Rosenfeld" wrote:

On Mon, 22 Aug 2005 08:36:52 -0700, "Di" wrote:

I have a column with over 11(K) date entries. This column grows daily. I
need a formula that will total the number of entries by month & year
(disregarding the day).

Column A = your list of date entries.

B1:Bn A list of dates being the first of each month of interest.

B1: 1 Jan 2000
B2: 1 Feb 2000
B3: 1 Mar 2000
etc.

If you put the above dates in B1 and B2; select B1&B2; place the cursor in the
lower right corner of the selection until it changes to a cross-hair, then
press the left mouse button and drag it down, it will increment automatically.

Copy down further into the future than you need.

Format the cells: Format/Cells/Number/Custom Type: mmm yyyy

C1: =COUNTIF(A:A,"="&B1)-COUNTIF(A:A,"="&B2)

Format as number.
Copy/Drag down as needed.




--ron

On Wed, 24 Aug 2005 07:49:33 -0700, "Di" wrote:

A variation of this worked!! Thanks for the help!

Glad to help. Thank you for the feedback.


--ron