Search code examples
excelexcel-tables

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

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?

Solution

  • Instead of SUM, use SUBTOTAL:

    =SUBTOTAL(9,TABLE[ColumnName])