Use a Calculated Field to reference an adjacent cell of a Pivot table to calculate a total

[LuckyDay]

I play a game (Animal Crossing: New Horizons) where I'm trying to collect "DIY Recipe Cards". I'd like to share a spreadsheet (google sheets) with people online who may be interested in helping me.

The idea is that I would post a link to the spreadsheet in a forum of other players, and then users can put their name next to the DIY Recipe Cards they have that I need (Column A). Based on the number of cards they have available and want to get paid for (by putting their name in Column A), a Pivot table will populate with their name, how many cards they plan to provide, and how much I will pay (the in-game currency is called NMT).

I can't seem to figure out what formula to use in a Pivot Table's Calculated Field to correctly calculate how much they will be owed (to be displayed in Column K) based on my rates posted in Columns D-G.

I tried to reference adjacent cells of the pivot table using the following formula, but it doesn't seem to work in Google Sheets at all. It works in Excel, but it's not dynamic so as new names are added and the pivot table expands, the Grand Total row may display a calculated total which I'm not looking for:

Please Login or Register  to view this content.

I've tried using Named Ranges and GETPIVOTDATA as well and only get errors.

Any tips/suggestions are greatly appreciated. Thanks!

Google Sheets Version of Spreadsheet

Please Login or Register  to view this content.

Full Disclosure: I've also posted this on Google Docs Editors Help but hadn't received a response from anyone.

Please Login or Register  to view this content.

[JeteMc]

Not sure why you chose to use the RC[-1] references as opposed to J2 etc and I have no idea what works with google sheets.
However the following will leave a blank cell on a "Grand Total" row and also on a row where no name is listed:

Please Login or Register  to view this content.

Paste the above formula into cell K2 and then drag the fill handle down to cell K10 for illustration.
Let us know if you have any questions.

[LuckyDay]

Thank you for this -- this was a much simpler solution than trying to force the field into the Pivot Table. Much appreciated.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.