Which returns the sum of Amounts for three tables named “West”, “Central”, and “East”. In the example shown, the formulas in L5:L7 behave like these simpler formulas: However, instead of hardcoding the table into each SUM formula, the table names are listed in column K, and the formulas in column L use concatenation to assemble a reference to each table. This allows the same formula to be used in L5:L7. The trick is the INDIRECT function to evaluate the reference. We start with: which becomes: and then: The INDIRECT function then resolves the text string into a proper structured reference: And the SUM function returns the final result, 27,500 for the West region. Note: INDIRECT is a volatile function and can cause performance issues in larger, more complex workbooks.
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.
