GETPIVOTDATA

GETPIVOTDATA(data_field, pivot_table [,field1] [,item1])

Returns the data obtained from a pivot table.

data_fieldThe name of the field you want to retieve data from.
pivot_tableThe reference to a cell in the pivot table report that contains the data.
field1(Optional) The name of the field that filters the data you want to retrieve.
item1(Optional) The value in the field that filters the data you want to retrieve.

REMARKS
* For an illustrated example refer to the page under Pivot Tables.
* Calculated fields or items and custom calculations are included in this function.
* The "data_field" must be enclosed in quotation marks and is the field that contains the data you want to retrieve.
* The "pivot_table" can be a cell reference, a named range or a label stored in a cell above the pivot table.
* If "pivot_table" is a range that includes two or more pivot table reports, data will be retrieved from whichever report was created most recently in the range.
* If "pivot_table" does not refer to a pivot table, then #VALUE! is returned.
* If "name" describes a single cell, the value of that cell is returned regardless of whether it is a string, number, error, and so on.
* If "name" does not describe a visible field, then #REF! is returned.
* The "name" can include multiple column and row headings with corresponding items in quotation marks, allowing you to retrieve specific data.
* You can have a maximum of 14 pairs of fields and items and they can be in any order.
* You can use the DGET function can be used to return a single value from a pivot table.
* You can use the PIVOTBY function to return the grouping of your data along two axis and aggregates the associated values.
* This function will always be single threaded.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABCD
1=GETPIVOTDATA("Sales",named_range,"Name", "David","Month","Jan") = 1100NameSalesMonth
2=GETPIVOTDATA("Sales",named_range,"Name", "Simon","Month","Jan") = 750David500Jan
3=GETPIVOTDATA("Sales",named_range,"Name", "James","Month","Feb") = 1500Simon750Jan
4=GETPIVOTDATA("Sales",named_range,"Name", "Simon","Month","Feb") = 850David600Jan
5=GETPIVOTDATA("Sales",named_range,"Month", "Jan") = 1850James1500Feb
6=GETPIVOTDATA("Sales",named_range,"Month", "Feb") = 2350Simon850Feb
7=GETPIVOTDATA("Sales",named_range,"Name", "David") = 1100James650Apr
8=GETPIVOTDATA("Sales",named_range,"Name", "Richard","Month","Feb") = 0Richard900Apr
9=GETPIVOTDATA("Sales",named_range) = 7150Simon1400Apr

1 - What is the sum of all the values in the "Sales" column where "Name=David" and "Month=Jan".
2 - What is the sum of all the values in the "Sales" column where "Name=Simon" and "Month=Jan".
3 - What is the sum of all the values in the "Sales" column where "Name=James" and "Month=Feb".
4 - What is the sum of all the values in the "Sales" column where "Name="Simon" and "Month=Feb".
5 - What is the sum of all the values in the "Sales" column where "Month=Jan".
6 - What is the sum of all the values in the "Sales" column where "Month=Feb".
7 - What is the sum of all the values in the "Sales" column where "Name=David".
8 - What is the sum of all the values in the "Sales" column where "Name=Richard" and "Month=Feb".
9 - What is the sum of all the values in the "Sales" column.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top