Get pivot table subtotal grouped date

Get pivot table subtotal grouped date

Generic formula 

=GETPIVOTDATA("data field",pivot_ref,"field","item")

Related formulas 

Get pivot table grand total

Get pivot table subtotal

Explanation

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:

=GETPIVOTDATA("Sales",$B$4,"date",3)

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:

  1. Grouped by month - use numbers 1-12
  2. Grouped by quarter - use numbers 1-4
  3. 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:

=GETPIVOTDATA("Sales",$B$4)

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 March, we need extend the formula with the field/item pair "Date" and 3:

=GETPIVOTDATA("Sales",$B$4,"date",3)

More specific subtotal

To get a more specific subtotal for the "Hazelnut" product in March, we add another field/item pair

=GETPIVOTDATA("Sales",$B$4,"date",3,"product","hazelnut")

Which returns $5,500 in the example shown above.

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.

Get pivot table subtotal grouped date

Generic formula 

=GETPIVOTDATA("data field",pivot_ref,"field","item")

Related formulas 

Get pivot table grand total

Get pivot table subtotal

Explanation

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:

=GETPIVOTDATA("Sales",$B$4,"date",3)

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:

  1. Grouped by month - use numbers 1-12
  2. Grouped by quarter - use numbers 1-4
  3. 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:

=GETPIVOTDATA("Sales",$B$4)

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 March, we need extend the formula with the field/item pair "Date" and 3:

=GETPIVOTDATA("Sales",$B$4,"date",3)

More specific subtotal

To get a more specific subtotal for the "Hazelnut" product in March, we add another field/item pair

=GETPIVOTDATA("Sales",$B$4,"date",3,"product","hazelnut")

Which returns $5,500 in the example shown above.

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.