GetPivotData for Multiple Items in Multiple Fields

[Chanzaiming]

Hi,

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}))

However, I am trying to do the following, but all I get is error:
=SUM(GETPIVOTDATA("Amount",'GL Data Pivot'!$B$3,"Department",{101,102,103},"Month",{1,2,3,4,5,6,7}))

What did I do wrong?

Thanks,

Yuming

 

[jeffreyweir]

Hi Yuming. I'm afraid you need to use multiple GETPIVOTDATA functions and add them together.
=SUM(GETPIVOTDATA("Amount",'GL Data Pivot'!$B$3,"Department",101,"Month",1)+GETPIVOTDATA("Amount",'GL Data Pivot'!$B$3,"Department",101,"Month",2) + GETPIVOTDATA("Amount",'GL Data Pivot'!$B$3,"Department",101,"Month", 3) + ....)

It's clunky but it works.

You can also use something called Range Slicing to identify and sum ranges within a PivotTable. I employed Range Slicing at this post to make a 3 way dynamic cascading data validaiton dropdown setup, but the same principles apply to summing.

http://forum.chandoo.org/threads/dynamic-drop-downs-with-large-table.18496/#post-112025

I plan to write up a blog post on this in the near future, but if you'd care to post a sample file here I can help you set Range Slicing up.

 

[NARAYANK991]

Hi Yuming ,

Have you tried :

=SUM(GETPIVOTDATA("Amount",'GL Data Pivot'!$B$3,"Department",{101;102;103},"Month",{1,2,3,4,5,6,7}))

The above as well as this :

=SUM(GETPIVOTDATA("Amount",'GL Data Pivot'!$B$3,"Department",{101,102,103},"Month",{1;2;3;4;5;6;7}))

work the same way.

Narayan

 

[jeffreyweir]

Well there you go! Thanks Narayan...for some reason I didn't realise that normal array stuff applies to the GETPIVOTDATA function. Thanks pal...love learning stuff I've overlooked.

 

[Chanzaiming]

Life savers!
Am I correct that Narayan's solution turns the Department field into an array?

Jeffrey, I appreciate your input. However, the reason I want to put them in a big bracket is so that I can easily update the formulas by simply replacing what's inside the bracket in all formula

Thank you guys!

 

[Vivek D]

I have pretty much the exact same requirement, however with one problem.
One of the combination of values does not exist.

In the above scenario, let's say Dept 101 and Month 1 combination does not exist in the data. It could at a later time.

Here's my formula
=SUM(GETPIVOTDATA("Amount",'Main Pivot'!$A$3,"Org","ABC","Win%",{"50-74%","75-100%"},"Phase",{"A";"B";"C"}))

The formula below ends up giving #REF error because Win% = "50-74%" and Phase = "A" combination does not exist int he data/pivot.

I tried putting an IFERROR around the GETPIVOTDATA but that just makes the full result 0.

=SUM(IFERROR(GETPIVOTDATA("Amount",'Main Pivot'!$A$3,"Org","ABC","Win%",{"50-74%","75-100%"},"Phase",{"A";"B";"C"}),0))

 

[jeffreyweir]

Vivek D: the IFERROR works for me. Are you array entering the formula?

 

[Sachin Bafna]

I have pretty much the exact same requirement, however with one problem.
One of the combination of values does not exist.

In the above scenario, let's say Dept 101 and Month 1 combination does not exist in the data. It could at a later time.

Here's my formula
=SUM(GETPIVOTDATA("Amount",'Main Pivot'!$A$3,"Org","ABC","Win%",{"50-74%","75-100%"},"Phase",{"A";"B";"C"}))

The formula below ends up giving #REF error because Win% = "50-74%" and Phase = "A" combination does not exist int he data/pivot.

I tried putting an IFERROR around the GETPIVOTDATA but that just makes the full result 0.
=SUM(IFERROR(GETPIVOTDATA("Amount",'Main Pivot'!$A$3,"Org","ABC","Win%",{"50-74%","75-100%"},"Phase",{"A";"B";"C"}),0))

Hi Vivek - What did you do to resolve the issue?
Jeffreyweir - The iferror makes the sum as 0, even if only one combination does not exist as what has been mentioned by Vivek.

 

[jnc]

Sachin/Vivek (assuming you haven't resolved this old problem)

Correct me if I'm mistaken, but if you simply switch the ordering so that the IFERROR is inside the SUM, then it should be fine b/c the order of operations will be correct in order to do what you wanted.

EDIT:

Wow I am blind,
Just looked at what you tried that didn't work. That is strange, since I agree with Jeff that the IFERROR around the GETPIVOTDATA should be the correct way. I believe this may have issues if more than one field is not existent for your data.

 

[Sachin Bafna]

IFERROR around GETPIVOTDATA does not work the way we want it to, because there are multiple items in the flower braces. It just picksup the first value alone which is not right.
I have a sample attached and you can see that its not working as expected.

 

[MrMGarza]

IFERROR around GETPIVOTDATA does not work the way we want it to, because there are multiple items in the flower braces. It just picksup the first value alone which is not right.
I have a sample attached and you can see that its not working as expected.

 

[Peter Bartholomew]

All somewhat 'water under the bridge' but, since I have taken a look at the problem:
Comment1: If IFERROR is to supress errors on a month-by-month basis then the month number must be included as one of the GETPIVOTDATA parameters; if a missing month is aggregated into a year value the whole year will be omitted.
Comment2: If an array of years is to be held as a row, then the months should be a column vector. Since we are talking about a financial year starting September it would make sense to number the months {9;10;11;12;1;2;3;4;5;6;7;8} to get data returned chronologically.
It would be necessary, of course, to adjust the financial years {2015,2016} by subtracting {1;1;1;1;0;0;0;0;0;0;0;0} to capture the initial month's data from the previous calendar year.

 

[Sachin Bafna]

Thanks Peter. This is helpful for me and for the larger community.

FYI - In the previous post attachment, Peter has created Name Variables.

Formula -> Name Manager -> Now you can create a new name variable and the values (just like an array).