Google sheets - "Anchor" formula to pivot table data which keeps moving

Question

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)

Answer 1

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.

Answer 2

You can add a "Calculated field" which sums Frequency and Qty, you will get a sum for every element with its respective Grand Total