# Documentation/Calc Functions/GETPIVOTDATA

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## Function name:

GETPIVOTDATA

## Category:

Spreadsheet

## Summary:

Determines a value inside a pivot table by using the specific identifying categories for that value (rather than a cell reference). Since the value is addressed using field and item names, the correct value is returned when the underlying data or layout of the pivot table changes. GETPIVOTDATA can be used in a formula when you need to access pivot table results elsewhere in your spreadsheet.

## Syntax:

Two different syntax definitions can be used:

- First syntax
- GETPIVOTDATA(
**Data field**;**Pivot table**[;**Field 1**;**Item 1**][; ... [**Field 126**;**Item 126**]])

- Second syntax
- GETPIVOTDATA(
**Pivot table**;**Constraints**)

Calc assumes the second syntax if exactly two arguments are given and the first argument is a reference to an existing pivot table; the first syntax is assumed in other cases. The Function Wizard uses the first syntax.

ODF 1.2 considers the first to be the "preferred" syntax, while the second is provided for "compatibility".

## Returns:

Returns a value from the specified pivot table. The type of value returned depends on the nature of the data displayed in the pivot table.

## Arguments:

### First Syntax

**Data field** is a string (in quotation marks), or a reference to a cell containing that string, that indicates which data field of the pivot table is to be used. Enter the required field name from the underlying data source (for example "Sales Value") or the name of the data field itself (for example "Sum – Sales Value"). If the pivot table has more than one data field, then enter the required field name. Even if the pivot table has only one data field, you must enter this argument correctly anyway.

**Pivot table** is a reference to a cell or a cell range that lies within the results area of the relevant pivot table, bearing in mind that a sheet can contain multiple pivot tables. The name of a named area can be passed in this argument. It is recommended that you always use the upper left corner cell of the area, to ensure that the chosen cell(s) will always be within the pivot table even if its layout changes.

**Field n** is a string (in quotation marks), or a reference to a cell containing that string, that identifies one of the pivot table's fields to be matched to calculate a partial sum (for example, "Employee" or "Region"). For each

**Field**argument, there should be a matching

*n***Item**argument.

*n***Item n** is a string (in quotation marks), or a reference to a cell containing that string, that corresponds to a value of

**Field**that is to be used to calculate a partial sum (for example, "Fritz" or "North"). For an item that is a number,

*n***Item**can alternatively be that numerical value.

*n*- If you enter only the first two arguments (
**Data field**and**Pivot table**), then GETPIVOTDATA returns the total result of the pivot table. - Each pair of
**Field**/*n***Item**arguments adds a constraint that the result shall satisfy. The order of the*n***Field**/*n***Item**pairs is not significant.*n***Field**and*n***Item**names are case-insensitive.*n* - If the pivot table contains only a single result value that fulfills all of the constraints, or a subtotal result that summarizes all matching values, that result is returned. If there is no matching result, or there are several with no subtotal for them, GETPIVOTDATA returns the #REF! error.
- There are several other conditions that will lead to GETPIVOTDATA returning the #REF! error. These include: the
**Data field**argument does not correspond to one of the pivot table's data field names; the**Pivot table**argument does not reference a cell or cell range within the pivot table; a**Field**argument does not correspond to one of the pivot table's field names; an*n***Item**argument is not a valid option for the corresponding*n***Field**value; a*n***Field**argument has no corresponding*n***Item**argument.*n*

### Second Syntax

**Pivot table** has the same meaning as in the first syntax.

**Constraints** is a string (in double quotation marks), or a reference to a cell containing that string, that specifies the data to be extracted from the pivot table. The string provides a space-separated list of constraints and entries can include single quotes where necessary. Entries in the list are case-insensitive and their order is not significant.

- One of the entries in the list of constraints can be a name that indicates which data field of the pivot table is to be used. Enter the required field name from the underlying data source (for example "Sales Value") or the name of the data field itself (for example "Sum – Sales Value"). The data field name can be omitted if the pivot table contains only one data field, otherwise it must be present.
- Each of the other entries in the list specifies a constraint, normally in the form
*Field*[*Item*] (for example, "Employee[Hans]" or "Region[South]"). However, if the item name is unique across all fields in the pivot table then the field name and square brackets may be omitted. - If
**Constraints**is set to the empty string, then GETPIVOTDATA returns the total result of the pivot table.

For the second syntax, GETPIVOTDATA reports a #REF! error in similar circumstances to those described for the first syntax.

## Additional details:

None

## Examples:

The following pivot table will be used as the basis for the examples that follow.

Formula | Description | Returns |
---|---|---|

=GETPIVOTDATA("Sum - Sales Value"; $A$1) | Here the function returns the grand total of the pivot table. Using the second syntax, equivalent formulas would be `=GETPIVOTDATA($A$1; "")` and `=GETPIVOTDATA($A$1; "Sum - Sales Value")` . |
408765 |

=GETPIVOTDATA("Sales Value"; $A$1; "Employee"; "Fritz") | Here the function returns the partial sum for employee "Fritz". Using the second syntax, equivalent formulas would be `=GETPIVOTDATA($A$1; "Sales Value Employee[Fritz]")` and `=GETPIVOTDATA($A$1; "Fritz")` |
105453 |

=GETPIVOTDATA("Sales Value"; $A$1; "Category"; "Tennis") | Here the function returns the partial sum for category "Tennis". Using the second syntax, equivalent formulas would be `=GETPIVOTDATA($A$1; "Sales Value Category[Tennis]")` and `=GETPIVOTDATA($A$1; "Tennis")` |
142699 |

=GETPIVOTDATA("sales value"; $A$1; "employee"; "hans"; "category"; "sailing") | Here the function returns the partial sum for employee "Hans" and category "Sailing". Using the second syntax, equivalent formulas would be `=GETPIVOTDATA($A$1; "Employee[Hans] Category[Sailing] Sales Value")` and `=GETPIVOTDATA($A$1; "Hans Sailing")` |
14060 |

## Related LibreOffice functions:

None.

## ODF standard:

## Related (or similar) Excel functions:

GETPIVOTDATA