sum for each month

[lovepeaceofmind]

hi, everyone:

I have two columns, column A and Column B. Column A is the date in
format of mm/dd/yyyy. Column B is the cost corresponding to each date.
How to get the sum of cost for each month in pivot table? I would also
like to know the count of dates for each month.

I appreciate your help!

Jorge

 

[Guest]

Since Col_A contains dates
Try this:

Right-Click on the Date column of the Pivot Table
Select "Group and Show Detail"
.........Check: Months
.........Click [OK]
That will group all of the dates by Month and reflect their respective totals.

If you want item counts by Month, too.
Right-Click on the Pivot Table an check: Pivot Table Wizard
Click the [Layout] button
Drag the Cost field into the DATA area of the Pivot Table, again
Double-click it and set the formula to Count
Click [Finish]

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Bernard Liengme]

A quick-and-dirty way would be to insert a new B column
In B1 enter =MONTH(A1) and copy down
Your pivot table would then use these values (you could generate text like
Jan, Feb... but this would give a sorting problem)
best wishes

 

[lovepeaceofmind]

Ron:

Thanks for your help! However, I do not want the months to be combined
if they belong to different years. How to prevent this?

Jorge

 

[Guest]

When you are setting the Grouping.....

Hold down the [Ctrl] key while you select Months AND Years.

The pivot table will then group Months by Years.

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Bernard Liengme]

Insert a column with =Year(date_column)
Make pivot table dragging first Year, then Date in the heading (row of
column)
Then use Ron's trick (which I had forgotten!)

best wishes

 

[lovepeaceofmind]

Ron:

Thank you so much. It works.

One more question. I will use the data from the pivot table to plot a
chart. From the excel, insert/chart/next/series/add/name (I put
Series1 as the name) /Value. How to make this VALUE part as a dynamic
range to reflect any newly added monthly cost? The reason is like
this: I will add in new months/years to my original data input area. I
have a code to update the PivotTable AUTOMATICALLY. Once I have a new
month, the pivot table will show this new month. Since the data source
of my chart is from the privot table, therefore, I need a dynamic
range for the Chart which needs to reflect this newly added month's
cost.

Please advise!

Thanks,

Jorge