How to use GETPIVOTDATA in Google Sheets

user_12974933 0
Viewed 1087

Sample Image

I'm trying to populate the field C4, which is the "# of Sessions" Grand Total (in this sample, M16) *10 / the "[redacted] Contribution Fee" Grand Total (in this sample, M15). But, I need it to work even if are more rows/columns added, etc. (e.g. I'm going to get numbers from November soon, so the Grand Total column will move to N).

In order to achieve this, I'm accustomed to using INDEX-MATCH or the like, but it looks like that doesn't work on Pivot Tables. So I've tried using GETPIVOTDATA instead. The problem is that I can't get the field to populate. I'm using,

=GETPIVOTDATA("SUM of How many sessions did you have this month?", A10)

I keep getting the ERROR "Field combination not found in pivot table for function GETPIVOTDATA."

I thought it was that I renamed "SUM of How many sessions did you have this month?" to "# of Sessions," but that doesn't seem to work either. Can someone help me understand what my problem is?

google-sheets-formula google-sheets pivot-table
1 Answers

for =M16*10/M15 try:

=INDIRECT(ADDRESS(MATCH("Contribution Fee", B:B,   0), 
                  MATCH("Grand Total",      11:11, 0)))*10/
 INDIRECT(ADDRESS(MATCH("# of Sessions",    B:B,   0), 
                  MATCH("Grand Total",      11:11, 0)))

for =M15*10/M16 try:

=INDIRECT(ADDRESS(MATCH("# of Sessions",    B:B,   0), 
                  MATCH("Grand Total",      11:11, 0)))*10/
 INDIRECT(ADDRESS(MATCH("Contribution Fee", B:B,   0), 
                  MATCH("Grand Total",      11:11, 0)))

enter image description here

user_56326290
Related