Although you can reference any cell in a pivot table with a normal reference (i.e. F8) the GETPIVOTDATA will continue to return correct values even when the pivot table changes. In this case, we want a subtotal of the “sales” field, so we provide the name the field in the first argument, and supply a reference to the pivot table in the second: This will give us the grand total. The pivot_table reference can be any cell in the pivot table, but by convention we use the upper left cell. To get the subtotal for the “Almond” product, we need to extend the formula as follows: Additional pivot table fields are entered as field/item pairs, so we have now added the field “Product” and the item “Almond”.
More specific subtotal
To get a more specific subtotal, like the “Almond” product in the “West” region, add an additional field/item pair: Note: GETPIVOTDATA will return a value field based on current “summarize by” settings (sum, count, average, etc.). This field must be visible in the pivot table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
