Formulas and Functions
Modifying this control will update this page automatically

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.

Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Helpful?
Character limit: 250
Maximum character limit is 250.
Thanks for your feedback.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.