# Google sheets - “Anchor” formula to pivot table data which keeps moving

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)` ## 2 Answers

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.

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