Getting Data from PIVOT

[illogical90]

Hello, I am using below formula in order to get the data from PIVOT but the thing is it is getting more bigger and bigger as I have to add the numbers of all the months. Can someone please suggest a smart way of doing so?

=IFERROR(SUM(GETPIVOTDATA(T(P$6),Pivot_EBTDA!$B$5,"Substream Leader (approves IL3 to IL4)",$B$42,"Business/Operational Unit",C5,"Initiative Execution Owner (Accountable)",D5,"Metric > Metric",$N$7,"Metric > Metric",$N$8,"Metric > Metric",$N$9,"Metric > Metric",$N$10,"Metric > Metric",$N$11,"Metric > Metric",$N$12,"Metric > Metric",$N$13,"Metric > Metric",$N$14,"Purpose",$K$7)+GETPIVOTDATA(T(P$7),Pivot_EBTDA!$B$5,"Substream Leader (approves IL3 to IL4)",$B$42,"Business/Operational Unit",C5,"Initiative Execution Owner (Accountable)",D5,"Metric > Metric",$N$7,"Metric > Metric",$N$8,"Metric > Metric",$N$9,"Metric > Metric",$N$10,"Metric > Metric",$N$11,"Metric > Metric",$N$12,"Metric > Metric",$N$13,"Metric > Metric",$N$14,"Purpose",$K$7)+GETPIVOTDATA(T(P$8),Pivot_EBTDA!$B$5,"Substream Leader (approves IL3 to IL4)",$B$42,"Business/Operational Unit",C5,"Initiative Execution Owner (Accountable)",D5,"Metric > Metric",$N$7,"Metric > Metric",$N$8,"Metric > Metric",$N$9,"Metric > Metric",$N$10,"Metric > Metric",$N$11,"Metric > Metric",$N$12,"Metric > Metric",$N$13,"Metric > Metric",$N$14,"Purpose",$K$7)),0)

 

[Jerry Sullivan]

Welcome to MrExcel,

Please post a small screenshot showing the layout of your PivotTable so we can see the relationship of the parts you are referencing in your formula.

 

[illogical90]

The parameters which I have to consider are as follows:

• Column B contains the divisions and every division represents a name listed in H5:H8. Network Logistics SW refers to H5, Network Logistics NE to H8 and Flow & Shipping to H6:H7
  
  .
• Column E "Impact" further divides in sub categories mentioned in column L8:L14. which means there are the cases where only few or more which are not listed here can be present in raw data but I have to pick only these.
• Then in the raw data there are three categories Planned, Actual and Forecasted. In this case we only have to consider Actual which is mentioned in column I7.
• In column N there are months of 2019 and I have to take January to YTD every time when I will create the report so it is another range which needs to be included.
• Raw data in present in Raw Data and PIVOT was created from this data. So how it should be done in a smarter way?

Regards.

 

[illogical90]

https://www.dropbox.com/s/yk7lkycl8yi6g56/Working FIle Pic.PNG?dl=0

 

[Jerry Sullivan]

Will you please post the workbook with the pivot table to dropbox, after changing any confidential data to random values?

 

[illogical90]

I have uploaded the file, I need to finalize this as early as possible. Will be looking forward for the help.

https://www.dropbox.com/s/3tut5fobo63oqn9/Working File.xlsm?dl=0

 

[Jerry Sullivan]

Thank you for posting that example workbook. The pivot table and some of the ranges have moved since your Original Post, so that makes it a little harder to follow your comments above.

Here's some comments and suggestions on how to simplify your formulas and better organize your data:

1. Consider reorganizing your raw data by "unpivoting" the months. Instead of having 12 columns for the months of the year, have one column with the first date of the month, and a second column for the value for that month. This makes your table have more rows and fewer columns. It's much easier to work with raw data that is normalized in this manner. For example, it allows you to just add the Month field to the Columns area of the Pivot, instead of adding 12 fields to the Values area.

2. For your month fields, don't use text values like "Jan-19". Instead use actual date values like 1/1/2019, and then use number formatting to have the value displayed as "Jan-19". This allows for sorting by date and calculation based on dates.

3. The way you are referencing the Leaders names in Column H is not a good practice. It causes you to modify the formulas in different rows of Columns E and F to point at the correct Leader. This is error-prone and takes more effort to maintain than having one formula that can be copied down the entire field. I'd suggest you add a field to your table for Leaders, and use VLOOKUP and a table that maps Substreams and Leaders as you described in Post #3 .

4. When using IFERROR in a formula that sums multiple GETPIVOTDATA expressions, you need to apply the IFERROR to each expression that might have an error. Your current formula has IFERROR on the outside, so if 11 of the GETPIVOTDATA expression return values and 1 returns and error, the Sum will be an error and the formula will return 0.

5. Regarding simplifying your formulas to handle multiple month references, if you normalize your raw data as I suggested in comment 1, you can simply filter your Pivot Table for the YTD months, then have your GETPIVOTDATA formula reference a Grand Total column. This isn't possible to do when you have separate raw data fields for each month, which is why you added a Grand Total field to the right of your Pivot.

6. You can use an Array formula to sum multiple GETPIVOTDATA expressions. Array formulas must be entered with Ctrl-Shift-Enter (not just Enter).
In the example workbook that I've posted to Box.com, I've used this array formula to calculate total IMPACTs in $H$3:

=SUM(IFERROR(GETPIVOTDATA("Value",Pivot_EBTDA!$A$3,"Initiative Execution Owner (Accountable)",$E3,"Substream Leader (approves IL3 to IL4)",$F3,"Business/Operational Unit",$D3,"Metric > Metric",P4:P6,"Purpose",$G3),0))

This array formula is a simpler way to get the same result as:

=SUM(
IFERROR(GETPIVOTDATA("Value",Pivot_EBTDA!$A$3,"Initiative Execution Owner (Accountable)",$E3,"Substream Leader (approves IL3 to IL4)",$F3,"Business/Operational Unit",$D3,"Metric > Metric",P4,"Purpose",$G3),0)+
IFERROR(GETPIVOTDATA("Value",Pivot_EBTDA!$A$3,"Initiative Execution Owner (Accountable)",$E3,"Substream Leader (approves IL3 to IL4)",$F3,"Business/Operational Unit",$D3,"Metric > Metric",P5,"Purpose",$G3),0)+
IFERROR(GETPIVOTDATA("Value",Pivot_EBTDA!$A$3,"Initiative Execution Owner (Accountable)",$E3,"Substream Leader (approves IL3 to IL4)",$F3,"Business/Operational Unit",$D3,"Metric > Metric",P6,"Purpose",$G3),0))


Here is a link to an example workbook that I've posted to Box.com
https://app.box.com/s/xz9iagy89efpl4zaza4jd9xuo994mza0