Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing data in a Pivot table
I have a pivot table with columns as months, rows as product codes, and the
body of the table in $ amounts. I want to sum a range of month, eg: Jun 06 to May 07. How can I do this? I don't want to use absolute column references as month change location as more/less data is added to the pivot table. Getpivotdata doesn't seem easy to nest in another formulas. Any suggestions? Many thanks -- Peter London, UK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing data in a Pivot table
Hi Peter
The GetPivotData function, is not the easiest to deal with and doesn't lend itself to Offset etc. I would set up a table on another sheet. In B1, enter the date for the first month of interest, e.g 01 Jun 2006 and custom format as mmm In C1 enter =DATE(YEAR(B1),MONTH(B1)+1,1) again formatted as mmm Copy C1 across through D1:M1 In A2 and onward enter the rows names you are interested in from the PT In B2 enter your GetPIvotData formula, referencing B$1 and $A2 and copy as appropriate. Do the summing from the rows of data created in this table. Changing the date in B1, will alter the range of 12 months that you pick up. -- Regards Roger Govier "Peter" wrote in message ... I have a pivot table with columns as months, rows as product codes, and the body of the table in $ amounts. I want to sum a range of month, eg: Jun 06 to May 07. How can I do this? I don't want to use absolute column references as month change location as more/less data is added to the pivot table. Getpivotdata doesn't seem easy to nest in another formulas. Any suggestions? Many thanks -- Peter London, UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing function in pivot table in column wise ?? | Excel Worksheet Functions | |||
Total Not Summing Correctly on Calculated Fields in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table column summing | Excel Discussion (Misc queries) | |||
summing values in a data table based on criteria in another column | Excel Worksheet Functions | |||
pivot table column summing | Excel Discussion (Misc queries) |