Summing data in a Pivot table

[Guest]

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

 

[Roger Govier]

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.