# How do you create a pivot table with the monthly sum of expenses? [closed]

Consider the following data:
|January|300€|
|January|100€|
|Febuary|10€|
|Febuary|5€|
|March|1000€|

The key here is that the month column is a DATE (2014-01-01, 2014-01-03) formatted as MMMM! How does one create a pivot table to display the monthly sum?

|January|400€|
|Febuary|15€|
|March|1000€|

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-03-01 22:06:28.990041

Sort by » oldest newest most voted

This post is a wiki. Anyone with karma >75 is welcome to improve it.

well mark everything from January to 1000 and then Data --> Subtotals --> choose the column with the money and on the right that the stuff should be summed up.

This at has nothing to do with the pivot table. I have never used the pivot table in calc as it is unintuitive... Maybe this is enough for your calculations. Maybe using the pivot tables afterwards might help you then. ;-)

more

I have a sheet for the raw expense data with a date for each line like mm/dd/yyyy. I have a pivot table already created with dates across the top and expense categories down the left side. Do we select the dates on the data tab of the spreadsheet, or on the pivot table itself?

EDIT: I found we click a date cell once, then do Data, Grouping and Outline, Group or F12. Then click in the Group By section, Months, then Years.

( 2015-07-12 15:33:41 +0200 )edit

Solution: The problem was that the Month was actually "=A1" formatted as MMMM, where column A was a complete date (YYYY-MM-dd). So while it displayed "January, Febuary...." in the sheet, the pivot table was using the underlying complete date.

By changing the month date to =Month(A1) one can get the expected result.

more

1

Hallo
No needs changing the Date itself, call →Data→Grouping... from one Date-Cell in Pivot-table instead.

Karolus

( 2014-08-12 12:13:04 +0200 )edit