Pivot table to plot sums of dates of each month

Question

In excel I have a column of days (mm/dd/yyyy) and I want to have a timeline sort of plot showing how many points I have for each month of each year. It's easy to show how many instances a day appears with a pivot table (added a column "Count" with just 1's), and then make a plot for that, but I don't know how to do that with just the month/year.

I tried using the =month and =year functions, and highlighted those columns (with the "Count" column) to make a pivot table, but I'm not sure how to group and plot it the way I want. Here's what it looks like when I have data point sums for month/day/year, I want the same thing but with the month/year:

And here's my data:

I'm sorry if this is a really low level problem, I'm a noob at pivot tables and have been trying this all day

Answer 1

You can group the data by month (or other useful options like quarter & year) IF values are formatted as actual dates. It looks like yours is formatted as a date but just in case, here are two ways to test...

A) Change format of a cell with date to number (this should return a date serial)
B) Use =ISTEXT(Select Cell With Date) (this should return FALSE)

Once valid date format has been verified

1. Click on a date in pivot table
2. Navigate to Analyze tab
3. Select Group Selection
4. You should get a pop-up asking how you want to group the data. Select Month
5. Ensure you are summarizing the points by Sum (right click on point cell in pivot and set option at Summarize Values By)

If you are using a pivot chart, it will update on refresh to reflect new row groupings (I.E. show monthly sums as you asked for)

Before & After photos below

---