Zoho Sheet GETPIVOTDATA Function: Pivot Filters and Reconciled Totals
Retrieve precise pivot aggregates with a complete sales fixture, exact field names, two-field filters and documented reference errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
GETPIVOTDATA retrieves an aggregate value from a pivot table, optionally limited by field/item pairs. It is useful for a summary report whose formulas should describe the intended slice of data rather than merely point at a result’s current screen position.
The pivot table must already exist and contain the intended aggregation. This function does not build or refresh the pivot by itself.
Availability and verification
The official GETPIVOTDATA reference was retrieved October 15, 2026. The original introduction release and last functioning version are unknown from the checked sources. Current web documentation does not establish every older, mobile or offline client’s behavior.
No native Zoho Sheet session was executed. Worked values are independent controls from complete fixtures, not screenshots or claims of live app output. Examples use the help’s semicolon formula notation; verify the actual editor’s accepted separator.
Syntax and exact field names
GETPIVOTDATA(data_field; pivot_table; [field_1; item_1; field_2; item_2]; ...)
data_field is the value-field name, supplied as quoted text or a cell reference. pivot_table is a reference to a cell or range within the pivot. Optional pairs identify a row, column or filter field and a specific item within it.
Zoho explicitly says field and item names must match the pivot’s capitalization and spaces. Retain each field with its paired item; a label such as Region without East or West does not specify the desired filter.
Complete sales dataset and pivot setup
Create a source table with these six records:
| Region | Product | Sales |
|---|---|---|
| East | Desk | 100 |
| East | Chair | 60 |
| West | Desk | 80 |
| West | Chair | 40 |
| East | Desk | 50 |
| West | Desk | 20 |
Create a pivot beginning at F1, using Region as a row field, Product as a column field, and Sales summarized by Sum. Configure the displayed value-field caption as Sales, keep subtotals/grand totals enabled, and verify F1 belongs to the actual pivot.
If the application uses another caption such as Sum of Sales, use that exact observed caption instead. The fixture specifies the caption to prevent an apparently correct formula from referring to a different field name.
Retrieve totals and a two-field intersection
=GETPIVOTDATA("Sales";$F$1) has independent target 350. Adding "Region";"East" gives 210; the equivalent West request gives 140.
=GETPIVOTDATA("Sales";$F$1;"Region";"East";"Product";"Desk")
The intersection target is 150, from the East/Desk records 100 and 50. The West/Desk target is 100, and the complete Desk total is 250. Chair totals are East 60 and West 40, summing to 100.
These values are independently grouped sums from the stated source table. They are not native pivot observations.
Advanced: build a reconciled summary
Zoho documents combining GETPIVOTDATA with other formulas. For the fixture, summing the East and West retrievals should give 350, agreeing with the grand-total retrieval.
The East share is 210/350 = 60% and the West share is 40%. Their sum is 100%. A different pivot filter or a stale source range can break that reconciliation even if each formula parses successfully.
Do not infer a missing region’s amount from this reconciliation without checking that all intended groups are represented. A report with only two displayed groups may not cover a changed source dataset.
Advanced: update the source and verify the pivot
Add a seventh source record, West / Chair / 10, and update the pivot to include it. The new independent targets are grand total 360, West total 150, Chair total 110, and West/Chair 50. East remains 210.
A formula still returning 350 may be reading a pivot that has not incorporated the new record. Verify the pivot’s source range and update state before editing a correct field/item expression.
Zoho says that referencing a pivot cell from an external formula beginning with = can generate GETPIVOTDATA automatically. Inspect the generated fields and items rather than assuming it selected the intended subtotal.
Errors and source limitations
The dedicated help lists #REF! for incorrect or missing field/item names, unavailable items, specified filtered-out-row cases, and disabled subtotals or grand totals. It also lists ordinary name and type errors. These documented conditions are not new native error executions.
For filtered-out rows, the source distinguishes a text-string field/item request from a cell-reference form. That does not establish that changing to a reference always recovers every hidden item; verify the intended filtered scope instead of treating references as a guaranteed bypass.
Its date-item guidance mixes a quoted slash-format example with wording about avoiding manually entered text. This guide uses no date filters and does not infer universal date parsing from that ambiguous sentence.
If a retrieval fails, check exact names, paired arguments, pivot membership and enabled totals. Do not silently convert a missing item to zero, since that can hide an incorrect report definition.