counting date entries by month & year

D

Di

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).
 
B

Blue Hornet

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
 
B

Bob Phillips

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)
 
R

Ron Rosenfeld

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).
Click to expand...

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
 
D

Di

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 doesn’t
count anything. Could I have the cells formatted incorrectly?
 
Top