Count number of events per month in a range

[cpots13]

I am trying to figure out this count function but am having some trouble.

I have many rows of events and Column A has the date in m/d/yy format.
I want to count the number of occurances for each month.
The function I tried was COUNTIF(A2:A512, "1/??/12") for January 2012
I then tried reformatting the date column to mm/dd/yy format making the criteria "01/??/12"
This didn't work. Any ideas?

Thanks

[oeldere]

In column B use the formula =month(a2)

After that you can make an pivot table.

You can use the month for the rows and the events for the counting (values)

[cpots13]

That works well, but I have multiple years of data and I would like to count each month for each year. I think I could add another row and list the years and have two criteria but I would rather not have to do this due to the amount of data I am going through.

If you or anyone else knows of a single function that could do this, it would help immensely.

Thanks!

[oeldere]

Make a table of your data

Excel 2007 => insert => table.

Then typ this formula in the columns.
In column B use the formula =month(a2)
In column C use the formula =year(a2)

The whole range will have this formula.

After that make an pivot table of your data.

[Kevin UK]

Hi cpots13

what about the Sumproduct function, for example:

Please Login or Register  to view this content.

[cpots13]

I am not really sure about the SUMPRODUCT function, I am not sure I understand how it would count the events.

I think if you see my data it may help?
Here is one of the sheets for an example:DateCount Example.xlsx
The yellow highlighted area is where the issue is (Column M)

Thanks

[oeldere]

with an pivot table.

see the attached file.

[cpots13]

Thanks oeldere!
I would still like to just use one formula if I can since I have so many sheets to go through though...your help is appreciated!

[oeldere]

If your data is all the same for all sheet, you can add all data to a summary sheet.

Then you can use pivot table, as I showed you.

[cpots13]

I feel kind of dumb Kevin UK, someone explained SUMPRODUCT() to me and I think it actually will work. Thank you for your help!

[oeldere]

See the forumrules about crossposting.

http://www.excelforum.com/forum-rule...rum-rules.html

See the link for the crosspost.

http://www.mrexcel.com/forum/excel-q...per-month.html