How to access a special column in the totals row of a table?

Regina Henschel 151

I have the following table for example:

Name	Sales	Region
John	23	West
Uwe	45	West
Mary	39	East
Doris	12	West
Peter	8	East
Total	127	5

The table is named 'myData'.

I can access the Totals row by myData[#Totals]. I can access the Sales column by myData[Sales]. But how to access the column Sales in the Totals row, so that I get the 127 in the example? The AI suggested answer myData[#Totals][Sales] does not work.

Regina Henschel 151 Reputation points

2024-07-22T12:57:57.75+00:00

The syntax =DeptSales[[#Totals],[Region]] given in https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e works neither.
Viorel 114.6K Reputation points

2024-07-22T18:12:57.04+00:00

Try to type = into formula bar, then click the “127” cell, and press <Enter>. The result will be =myData[[#Totals],[Sales]] or =myData[[#Totals];[Sales]] depending on the current separator.
Regina Henschel 151 Reputation points

2024-07-22T21:43:39.6933333+00:00

@Viorel: Your trick works. For my environment it is =myData[[#Totals];[Sales]].

It should be mentioned in the above linked "using-structured-references-with-excel-tables" that the separator might be a semicolon.

Share via

How to access a special column in the totals row of a table?