GetPivotData Multiple Fields

[Bazy2k]

Morning guys.

I have a pivot table which looks like this...

COST CENTRE
NUMBER VET IT OTHER

7800 750.00 100.00 76.00


In order for me to obtain the sum of the VET and IT cost centre amounts only
for the account code 7800 i am currently using the following formula:

=GETPIVOTDATA("Sum Of
GoodsValueInBase",'PivotTab'!$A$3,"Number","7800","CostCentre","VET") +
GETPIVOTDATA("Sum Of
GoodsValueInBase",'PivotTab'!$A$3,"Number","7800","CostCentre","IT")

So basically i am picking off the two entries from the pivot table
seperately and adding them together.
Is there any way i can use the getpivotdata function to look up them both
and add them together (the formula bar runs out of room if i am using too
many accountnumbers and cost centres, so i am looking for a shorter formula)

Something like
=GETPIVOTDATA("Sum Of
GoodsValueInBase",'PivotTab'!$A$3,"AccountNumber","7800","AccountCostCentre","VET" & "AccountCostCentre", "IT")


Thanks in advance
Bazy2k

 

[Roger Govier]

Hi

That can't be done with GetPivotData.
You will need to add the various elements together.
However, you can cut down the size of the formula by using Index and a named
range instead.

In my case the PT was on Sheet 4, with the PT in columns A.
I used Column I to contain the Costcentre required.
Adjust the following to suit your layout.

Insert>Name>Define>Name> Row >Refers to>
=MATCH(Sheet4!$I1,Sheet4!$A:$A,0)

In cell J2 enter
=IF(ISNA(Row),"",INDEX(B:B,row)+INDEX(C:C,row))

Just keep adding the extra columns as required

 

[Roger Govier]

Hi

That can't be done with GetPivotData.
You will need to add the various elements together.
However, you can cut down the size of the formula by using Index and a named
range instead.

In my case the PT was on Sheet 4, with the PT in columns A.
I used Column I to contain the Costcentre required.
Adjust the following to suit your layout.

Insert>Name>Define>Name> Row >Refers to>
=MATCH(Sheet4!$I1,Sheet4!$A:$A,0)

In cell J2 enter
=IF(ISNA(Row),"",INDEX(B:B,row)+INDEX(C:C,row))

Just keep adding the extra columns as required

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Several more methods.
http://www.mediafire.com/file/twngjmxmoy4/05_14_09.xlsx

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Several more methods.
http://www.mediafire.com/file/twngjmxmoy4/05_14_09.xlsx

 

[Bazy2k]

Thanks guys for both your help, i took the easy option in the end, simply
retrieved the data for the two cost centres seperately and then just added
them together on my spreadsheet, think i was trying to be too clever about it!

Thanks again!

 

[Bazy2k]

Thanks guys for both your help, i took the easy option in the end, simply
retrieved the data for the two cost centres seperately and then just added
them together on my spreadsheet, think i was trying to be too clever about it!

Thanks again!