GetPivotData function errors, basic use.

Question

Level 1

19 points

GetPivotData function errors, basic use.

I have a numbers sheet for logging my biking miles. I'd like to get clever about forecasting how many monthly and annual miles I have to bike to reach my goals.

I have a pivot table that neatly summarizes my progress to date:

And a table with monthly and annual goals with forecasts for needed miles/day to reach those goals:

As you can see I get a syntax error. I've tried all sorts of alternatives for the GetPivotData arguments within success. Anyone able to show me what I've done wrong / what I need to do to make it work? I can share the spreadsheet if asked.

Thanks.

Mac mini, macOS 15.1

Posted on Jan 6, 2025 8:21 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,456 points

Jan 7, 2025 7:54 AM in response to publicaldo

A few of things:

That "formula" is definitely going to cause a syntax error. The function name (SUMIFS, I presume) is missing. The column names are in quotes, they are not references to ranges in the table.
I don't use pivot tables much but I have noticed when using a pivot table for the data, the results of the formula might change as you expand/collapse the categories in the pivot table. Besides, there is no need to use the pivot table for your formula. Use the original table of data.
When using "months" and "years" as columns in the pivot table, they are dates, not text, so comparing them to the strings "2025" and "January" will always be false (no matches). 2025 is actually Jan 1, 2025. January is actually January 1 of some year, perhaps the earliest year from your table. This isn't going to work well.

Using the original table as the source data:

=SUMIFS(Rides::B,Rides::A,">="&DATE(2025,1,1),Rides::A,"<"&DATE(2025,2,1))

Sums values in B where A is >=Jan 1, 2025 and A is <Feb 1, 2025

Reply

Answer 2

SGIII

Level 8

37,550 points

Jan 8, 2025 8:08 AM in response to publicaldo

You've got GETPIVOTDATA in the title of the post, but the function seems to be missing from your screenshots.

If you haven't done so already, have a look at the explanation and examples here:

GETPIVOTDATA - Apple Support

Pivot tables are powerful and flexible! They're also easy to use for most purposes. There is almost certainly a way to save yourself a lot of time you'd otherwise be spending setting up and debugging calculations.

But if things get too complicated with GETPIVOTDATA, remember that you can let a simple pivot table do the calculations for you (as you have already done) and then create a new blank "regular" table, collapse the pivot table down to show just the monthly cells you want, select the visible cells in the pivot table, command-c, and paste into the new table.

You'll end up with the summary figures in a tabular format in a familiar "regular" table and you can then add your goals next to them.

SG

Reply

Answer 3

publicaldo Author

Level 1

19 points

Jan 9, 2025 1:57 PM in response to SGIII

Hah! Got it to work. I added calculated columns for the month and year in the data/log table rather than using the date parsing that categories and pivot tables can do. It uses numbers for the months instead of names - but that's fine for now. I added a number column with white colored font to the left of the month for reference.

Monthly miles works and updates with new month entries:

Current month and current annual daily targets work to with simplified math.

Reply

Answer 4

SGIII

Level 8

37,550 points

Jan 9, 2025 10:35 PM in response to publicaldo

Upvote for what is I believe is the first practical use of GETPIVOTDATA in these forums!

SG

Reply