4

I have numbers coming in every day from a variety of partners using a nightly report form. I have a pivot table for this data to summarize various counts. I have a list of goals for each partner in a separate tab.

I would like to create a calculated field in the pivot table that can reference this goals list so I can make a "Progress Towards Goal" field in the pivot table. Example below.

enter image description here

Is this possible? Is there a smarter way to do this?

Tomulent
  • 531
  • 2
  • 5
  • 20

1 Answers1

7

You can use VLOOKUP in a calculated field just like you would elsewhere!

From your description and the values in your example, I'm not entirely certain what you'd like 'Progress toward goal' to summarize (the progress made that day? or progress to date?), but here's a general example of how to do a VLOOKUP inside a calculated field:

If "Goals" was a sheet with Partner Name in A and a Total Widget Goal in B, you could get "Progress Toward Goal" as

=SUM('Widgets Produced')/VLOOKUP(Partner, Goals!$A$2:$B, 2, FALSE)

and selecting "Custom" for "Summarize by": "Custom" selected for "Summarize by"

Note that this would show you the % total progress made in the day shown. If you want to get a total-to-date progress, the easiest approach is to add a running total widget count by partner to your source data to use as the numerator in your Calculated Field.

Kate
  • 1,263
  • 10
  • 13
  • I don't think you can! Or at least, I can't. After a few seconds, Google Sheets turns it into a “#REF” error, like in this other question. https://stackoverflow.com/questions/69115226/using-vlookup-in-calculated-field-in-google-sheets-pivot-table-is-unstable-and-g – obskyr Nov 20 '22 at 10:50