Ask Your Question
0

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

asked 2014-08-12 11:43:25 +0200

mmalmeida gravatar image

updated 2014-08-12 11:49:44 +0200

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 flag offensive 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

2 Answers

Sort by » oldest newest most voted
0

answered 2014-08-12 12:00:55 +0200

this post is marked as community wiki

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. ;-)

edit flag offensive delete link more

Comments

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.

bulrush gravatar imagebulrush ( 2015-07-12 15:33:41 +0200 )edit
0

answered 2014-08-12 11:59:16 +0200

mmalmeida gravatar image

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.

edit flag offensive delete link more

Comments

1

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

Karolus

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

Question Tools

1 follower

Stats

Asked: 2014-08-12 11:43:25 +0200

Seen: 5,008 times

Last updated: Aug 12 '14