0

I have set up 3 sheets in Google sheets in related to budgeting/expenses.

The first contains the full budget (say Sheet A for example); The second contains the expenses (Sheet B); and the Third contains the pivot table (Sheet C), which helps to group together all the different expenses from Sheet B into categories.

The pivot table is used to separate all the expenses into their relevant category and provide a sum total of the amount spent within that particular category. I want to copy this sum total amount to Sheet A. At first, I set a simple formula

Excel Formula: ='Sheet C'!C5

But the problem is, every time I add a new expense to Sheet B, the sum total value will be moved down to say C6 and so on. Is there some kind of formula I can use to copy the sum total value to Sheet A?

1

1 Answer 1

0

Use the getpivotdata() function. Try this in cell 'Sheet A'!F6:

=getpivotdata("SUM of Requested", 'Sheet C'!A$1, "Budget catagory and description", C6)

Alternatively, get the sum directly from the source data, like this:

=sumif('Sheet B'!B$4:B, C6, 'Sheet B'!D$4:D)

...or:

=query('Sheet B'!B$4:D, "select sum(D) where B = '" & C6 & "' label sum(D) '' ", 0)

2
  • How would that work? Here's an example of the spreadsheet I mentioned - docs.google.com/spreadsheets/d/… I want the sum total value in the pivot table to be shown and updated in the 'Requested' section of sheet A. But every time a new value is entered in Sheet B, the order of the pivot table and the sum value changs.
    – JW2022
    Apr 28 at 9:46
  • Edited the answer per your sample spreadsheet. Apr 28 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.