When rows or columns are added or removed from either table, the formula will continue to return correct results. In addition, the formula will work even if the tables are located on different sheets in a workbook.
Alternative syntax with Total row
It is also possible to reference the total row in a table directly, as long as tables have the Total Row enabled. The syntax looks like this: Translated: “The value for Amount in the Total row of Table1”. Using this syntax, the original formula above could be re-written like this: As above, this formula will work even when the table is moved or resized. Note: the total row must be enabled. If you disable a total row, the formula will return the #REF error.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
