The GETPIVOTDATA function allows the retrieval of a value from a PivotTable report while its structure is being changed.



Simply select any cell outside the PivotTable report, press the equals (=) symbol and select a cell in the PivotTable reports Values area.



Select File -> Excel Options -> Formulas -> clear the checkbox in Use GetPivotData functions for PivotTable references in Working with Formulas section.




Advantage: The SUMIF function recognizes text such as "2003", "Qtr1" or "Grand Total", see cells C1:C3 and results in cells D1:D3 in the screenshot below.