change summary from daily to monthly

[breilly00]

I have an excel sheet that has a row for each day. Each day has the total of cars that have exceed the speed limit as recorded in our speed sign. 2 columns have a date/time that is in MM/DD/YYYY format and a count of cars exceeding the speed limit and has approx. 3 years of data. The end goal is to create a graph that shows the total number of cars that exceed the speed limit for each month.

I need to find the best way to add up the count of vehicles speeding within a month. So far I have thought of creating a separate worksheet that lists the unique month and years in the sheet and then use a sum if feature to look thru the original sheet that matches the month and year of the date field and add those finds to the monthly total in the separate sheet.

I also thought of adding another column that extracts the month and year from the date/time column and work with that new column to calculate the counts for each month.

Other options I have thought of are to do a macro, which I am a real novice using hat approach, figuring out how to have formulas in the new sheet that gives me totals by month and not by day.

I could use some suggestions as to the best way to accomplish this. One more thing, each month, new totals, by day, will be added to the sheet. So, the solution needs to be versatile enough to handed creating new monthly graphs.

 

[Z51]

A Pivot Table should be able to quickly summarize your data by month. Select your data range (including headers), then click Insert > Pivot Table (on new worksheet). Drag the Count field to the Values section, then the DateTime field to the Rows section. It may auto-summarize by Months and Days, but if not you can right-click on one of the dates in the pivot table and click Group... then choose both Days and Months and click OK.

You can then easily create a line graph (or other type) by selecting that graph type on the Insert toolbar.

 

[breilly00]

I heard about those things (pivot tables) in my youth. After some you tube videos and playing around it gave me what I needed, My greatest thanks to "Z". The response pointed me in the right direction. Mr. Excel you Da man !!!!!!!!!!