To get the subtotal for a value field in a pivot table that has been grouped by date, you can use the GETPIVOTDATA function and a number that corresponds to the date grouping. In the example shown, the formula in I7 is:
Although you can reference any cell in a pivot table with a normal reference (i.e. E10) the GETPIVOTDATA will continue to return correct values even when the pivot table changes.
How this formula works
To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. When dates are grouped, they can be queried based on the numeric equivalent:
Grouped by month - use numbers 1-12
Grouped by quarter - use numbers 1-4
Grouped by year - use year numbers (2012, 2013, etc.)
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:
To get the grand total for a value field in a pivot table, you can use the GETPIVOTDATA function. In the example shown, the formula in I6 is: = GETPIVOTDATA ( "Sales" , $B$4 ) Although you can reference any cell in a pivot table with a...
To get the subtotal for a value field in a pivot table, you can use the GETPIVOTDATA function. In the example shown, the formula in I6 is: = GETPIVOTDATA ( "Sales" , $B$4 , "Product" , "Almond" ) Although you can...
The Excel GETPIVOTDATA function can query a pivot table and retrieve specific data based on the pivot table structure, instead of cell references.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.