To pull data from a cell in a pivot table, you can use a normal cell reference, such as =B5, or you can use the GetPivotData function, which is specially designed to extract data from a pivot table.
The advantage of the GetPivotData function is that it uses criteria, so the correct data will be returned, even if the pivot table layout is changed.
Get the Subtotal Amount
Usually, the GetPivotData function works well, and returns the correct result. In the screen shot below, I typed an equal sign in cell B1, and then clicked on cell B8, where the Bars subtotal is located.
A GetPivotData formula was automatically created, and it returns the quantity of Bars sold.
=GETPIVOTDATA(“Quantity”,$A$3,”Category”,”Bars”)
Create Custom Subtotals
One of the features of a pivot table is that you can create multiple subtotals for a field. For example, we could show both the Sum and an Average for the quantity of units sold.
However, when you use these Custom Subtotals, the GetPivotData formula might show an error.
Problems with Custom Subtotals
The sample pivot table now has custom subtotals – Sum and Average. Now, if I type an equal sign and click on either of the Bars subtotal cells, the result is a #REF! error. The GetPivotData formula looks different too, with square brackets in it.
=GETPIVOTDATA($A$3,”Category[Bars;Data,Sum]”)
Fix the GetPivotData Formula
Fortunately, it’s easy to fix the #REF! error – you just remove the “Data,” from the GetPivotData formula. In this example, the corrected formula is:
=GETPIVOTDATA($A$3,”Category[Bars;Sum]”)
With that simple change to the formula, the correct result is returned.
Top or Bottom Subtotals
These list-style GetPivotData formulas are only created if there are Custom Subtotals, at the bottom of the pivot field group. For Automatic Subtotals, or Custom Subtotals at the top of the group, a normal GetPivotData formula is inserted.
Note: If there are multiple subtotals for a field, the subtotals cannot be shown at the top of the group, so they would automatically move to the bottom.
For more information on the GetPivotData formula, please click here to visit my Contextures website.
_______________