How to have a structured reference respond to filters on a table?-CodePudding

I have a table in Excel with multiple columns. One of the columns is an index column with an arbitrary number (1,2,3..). At the moment I have a pivot table where the rows of the pivot table are in sync with the rows of the source table, even when filters/sorts are applied.

I am using a structured reference to calculate the sum of a column in the source table: =SUM(TABLE[ColumnName]) But this number remains constant even when filters are applied to TABLE.

How can I make this structured reference responsive to filters applied to TABLE?

CodePudding user response：

Instead of SUM, use SUBTOTAL:

=SUBTOTAL(9,TABLE[ColumnName])