GETPIVOTDATA
The GETPIVOTDATA function returns aggregate data from a pivot table.
GETPIVOTDATA(aggregate-field, pivot-cell, field-name, item, field-name…, item…)
aggregate-field: The name of the field that contains the data you want to use.
pivot-cell: A cell that is used to identify the pivot table you want to reference.
field-name: An optional reference to a field in a pivot table, which further narrows down the data you want. field-name must be followed immediately by item.
item: If an optional field-name is included, item is a specific value within that field.
field-name…: Optionally include one or more additional references to a field in a pivot table, which further narrows down the data you want. Each field-name… must be followed immediately by item…. This pattern of field-name…, item… can be repeated as many times as needed.
item…: If an optional field-name… is included, item… is a specific value within that field. There must be one item… following each field-name…; therefore, this function always has an even number of arguments.
Notes
This function is only available in Numbers.
field-name and item arguments must be enclosed in quotation marks, other than number values.
Examples |
|---|
Given the following pivot table (titled Student Grades Pivot): |
A | B | C | |
|---|---|---|---|
1 | Name | Class | Grade (Average) |
2 | Student 1 | 1 | 91 |
3 | 2 | 94 | |
4 | 3 | 82.5 | |
5 | Student 1 Total | 89.17 | |
6 | Student 2 | 1 | 94 |
7 | 2 | 91.5 | |
8 | 3 | 91.5 | |
9 | Student 2 Total | 92.33 | |
10 | Grand Total | 90.75 |
=GETPIVOTDATA(Student Grades Pivot::$C$1,Student Grades Pivot::$C$2), returns 90.75, the average grade for Students 1 and 2. =GETPIVOTDATA(Student Grades Pivot::$C$1,Student Grades Pivot::$C$2, "Name", "Student 1"), returns approximately 89.17, the average grade for Student 1. =GETPIVOTDATA(Student Grades Pivot::$C$1,Student Grades Pivot::$C$2, "Name", "Student 2", "Class", 1) returns 94, the average grade earned by Student 2 in Class 1. |