CODEWITHSUNDEEP
excelpivot-table
user985952
user985952

Reputation: 97

Summarize data incrementally in Excel for each month

I'm using Excel 2007, and I have a report that outputs transaction lines with a date, sales, inventory value, and quantity columns. Looks something like this:

Date        Sales   Value   Quantity
1/1/2015    500     300     3
2/15/2016   25000   18000   10
3/2/2016    15000   10000   8
4/20/2016   200     100     1

I need to get a pivot table or some other way to summarize the data, so I have months of 2016 at the top that sums all the amounts from the previous months. So any lines with a date before or equal to 1/31/16 would fall into Jan-16. Any lines with a date before or equal to 2/29/26 would fall into Feb-16.

It would look something like:

Jan-16           Feb-16           Mar-16           Apr-16
Sales Value Qty  Sales Value Qty  Sales Value Qty  Sales Value Qty
500   300   3    25500 18300 13   40000 28300 21   40100 28400 22

I tried to columns for each month that looks at the date and displays if it fall into that month or not, but the pivot table can't summarize it correctly since it's not all rolled up into 1 column. And I don't know how to summarize multiple values into 1 column.. anyone know a good solution for this?

Upvotes: 0

Views: 829

Answers (3)

Wedge
Wedge

Reputation: 1826

I don't use pivot tables much, so there probably is a way to do it totally inside of one. Alternatively though you can just make a table with your rolling date ranges as the left column and use a sumif formula to get the sum of every entry that was before that date. The table would be like:

Month       Sales   Value   Qty
1/31/2016   500     300     3
2/29/2016   25500   18300   13
3/31/2016   40500   28300   21
4/30/2016   40700   28400   22

And you have a formula like this in the first row of the sales column for example

=SUMIF(yourdataDateColumn,"<"&$A2,yourdataSalesColumn)

To get the values you want. Then you can use that table to generate a pivot table that has the month values as columns and the Sales/Values/Qty totals as values that show up as under each month.

Upvotes: 0

Andreas
Andreas

Reputation: 23958

Add another column with =month(A2) if A2 is the datecell.
This will create a new item that is 1-12 (month number) and this can be used in the column in the pivot table.

Upvotes: 0

seppdepp3
seppdepp3

Reputation: 13

create a pivot, Date for rows.
Right-click into the rows column of the pivot table and select "group". Here you have the option to summarize by month

Upvotes: 1

Related Questions