• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

GetPivotData for Multiple Items in Multiple Fields

Chanzaiming

New Member
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
 
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.
 
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
 
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.
 
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!
 
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))
 
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.
 
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.
 
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.
 

Attachments

  • GetPivotData Issue.xlsx
    27.3 KB · Views: 34
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.
 

Attachments

  • GetPivotData Issue.xlsx
    27.1 KB · Views: 33
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.
 

Attachments

  • GetPivotData Issue (pb).xlsx
    27.1 KB · Views: 63
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).
 
Back
Top