Google Sheets – Anchor Formula to Pivot Table Data

google sheetspivot table

Ive got a Pivot table in a Google Sheet which summarises some numerical data by groups.

Ive attached a screenshot of the pivot table below.

I want to be able to sum the values of the "grand total" in B and C. eg =sum(b7:c7) which i can do, but as soon as another element is added to the raw data or a filter is applied to the pivot table the grand total row moves and my sum formula breaks.

Is there a way i can "anchor" my forumla to the grand total values, something like this pseudo code : sum($grand_total_of_Frequency : $grand_total_of_Qty)

screenshot of pivot table

Best Answer

You could use a QUERY formula combined with the SUM function:

=sum(QUERY(A1:C11,"select sum(B), sum(C) where A<>'Grand Total'"))

What you actually do is to recreate and sum the sums from the Pivot Table itself, omitting the values of the Grand Total.

You can place the formula wherever you like.

Related Topic