MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting months


Posted by Paul on February 01, 2001 8:36 AM

How do you count the number of times the months, Jan- Dec, appear in a column? Example in column A I have the days when work was started, 1-1-01, 2-1-01, 2-2-01 etc. I want to count how many times the mouths are in column A:A in this example Jan=1, Feb=2. Thanks for your help in advance. Paul


Posted by Kaiowas on February 01, 2001 8:42 AM

In cell B1 enter the formula =Month(A1) and fill it down to the bottom of your list of dates. Then in the first 12 rows of column C list the numbers 1 to 12. In cell D1 enter =Countif(B:B,C1) and fill this down to row 12. Cells D1:D12 will now contain the values you are looking for.

Kaiowas

Posted by Mark W. on February 01, 2001 9:05 AM

...Or just add a field name such as "Date" in the
cell above the date values and produce a PivotTable
with "Date" in the COLUMN area "Count of Date" in
the DATA area. Group the PivotTables column field
by Months (and probably Years)... and there you have
it!