CODEWITHSUNDEEP
excelexcel-tables
MaxxL
MaxxL

Reputation: 15

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?

Upvotes: 0

Views: 361

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

Instead of SUM, use SUBTOTAL:

=SUBTOTAL(9,TABLE[ColumnName])

Upvotes: 1

Related Questions