Pivot Table – How to copy sum value onto another sheet

googlegoogle sheetsgoogle-sheets-query

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?

Best Answer

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)

Related Topic