GetPivotData for Multiple Items in Multiple Fields

[Josefin]

Hi,

I'm having a problem similar to one that was solved in this forum. The original one was the following:

"Is it possible to use GetPivotData formula to summarize data by mulitple items in multiple fields?

I understand I can always use SUMIFS, but it's WAY slower than the pivot table when it comes to thousands of cells updating at the same time.

I also know that I can do it for multiple items in 1 field, such as:
=SUM(GETPIVOTDATA("Amount",'GL Data Pivot'!$B$3,"Department",101,"Month",{1,2,3,4,5,6,7}))"

My problem is that I have tons of different possible fields, so instead of having something like {1,2,3,4,5,6,7} I would like to be able to chose a range of cells (for example {A1:A200}). Nevertheless, when I try that, an error in the formula appears and excel doesn't allow me to put a range of cells

I would appreciate any help!

Thank you

 

[Deepak]

Can we have the sample so that one may do something for you.

 

[Josefin]

I can't upload the data . But it is simply being able to select a range of cells (again the same example A1:A100) instead of adding one hundred "getpivotdata" formulas, which seems to be the only way (ex SUM(getpivotdata("A",Pivottable,field 1,A1)),(getpivotdata("A",Pivottable,field 1,A2)),(getpivotdata("A",Pivottable,field 1,A3)), and so on

 

[Deepak]

Then you can also direct refrer ranges for sum with help of if/sumproduct/match/sum/sumif....depending on your simplicity.

In order to get help one should to help us too..

 

[jeffreyweir]

Josefin: Check out my post at http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/

This will do exactly what you need.