Using GETPIVOTDATA on custom group subtotals

[miconian]

I have two perfectly functional formulas that pull data from a pivot table. I want a formula that actually just adds those two numbers together

The working formulas I have are:

=GETPIVOTDATA("total_imps",C87,"site_rollup","cbs","platform_type","Desktop","ad_grouping","Native Slot - Native Served","month_id",'F5 2016 new'!X1)
=GETPIVOTDATA("total_imps",C88,"site_rollup","cbs","platform_type","Desktop","ad_grouping","Native Slot - Non Native IAB Standard Served","month_id",'F5 2016 new'!X1)

The important part here is the group called "ad grouping." You can see that the only difference between the formulas above is that they each reference a separate item within "ad grouping."

I was thinking that instead of making an unwieldy SUM to combine the two formulas above, I would create a new custom group that combines the two "ad_grouping" items. I created it. The new group is called ad_grouping2, and the new item is called NativeSlotTotal.

But I have not been successful in referencing that new grouping with a formula. Here is something I tried:

=GETPIVOTDATA("total_imps",C88,"site_rollup","cbs","platform_type","Desktop","ad_grouping2","Native SlotTotal","month_id",'F5 2016 new'!X1)

This returns #REF.

So I found this old article[url] about solving the problem in Excel 2003. I am using Excel 2016, but it seemed like the same problem. So, going with the recommendation in that article, I tried this:

=GETPIVOTDATA("total_imps",C88,"site_rollup","cbs","platform_type","Desktop","ad_grouping2"["ad_grouping2";SUM],"Native SlotTotal","month_id",'F5 2016 new'!X1)

The key part of the above being:

"ad_grouping2"["ad_grouping2";SUM],"Native SlotTotal"[/]
Where I was attempting to imitate this:
[code]  GETPIVOTDATA(<AnchorCell>,"<GroupName>[<GroupItem>;<FunctionName>]")

But Excel doesn't even recognize that as a formula.
Any thoughts on what I'm doing wrong?

I also tried this as an array:

=GETPIVOTDATA("total_imps",C88,"site_rollup","cbs","platform_type","Desktop","ad_grouping","{"Native Slot - Native Served","Native Slot - Non Native IAB Standard Served"},"month_id",'F5 2016 new'!X1)

But Excel doesn't seem to like the dashes there, which it treats as minus signs.

Many thanks.

 

[Jerry Sullivan]

I was thinking that instead of making an unwieldy SUM to combine the two formulas above, I would create a new custom group that combines the two "ad_grouping" items. I created it. The new group is called ad_grouping2, and the new item is called NativeSlotTotal.

But I have not been successful in referencing that new grouping with a formula. Here is something I tried:

=GETPIVOTDATA("total_imps",C88,"site_rollup","cbs","platform_type","Desktop","ad_grouping2","Native SlotTotal","month_id",'F5 2016 new'!X1)

This returns #REF.

I think this should have worked unless you have a name mismatch (e.g. "Native SlotTotal" vs. "NativeSlotTotal"). Try letting Excel generate the GetPivotData formula for you by selecting a cell outside the Pivot, entering "=" (without quotes), then selecting that grouped subtotal PivotItem.

You'll need to have the default option checked if it isn't already: File > Options > Formulas > Working with formulas > Use GetPivotData functions for PivotTable references.

If you would rather not use Grouping to accomplish your Summing, you could use an array formula like this (confirmed with ctrl+shift+enter not just enter):

=SUM(IFERROR(GETPIVOTDATA("total_imps",C87,"site_rollup","cbs","platform_type","Desktop","ad_grouping",
   "Native Slot - " & {"Native Served","Non Native IAB Standard Served"},"month_id",'F5 2016 new'!X1),0))