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:
To do a two-way lookup in an Excel Table , you can use the MATCH function with a structured reference and VLOOKUP. In the example shown, the formula in I5 (copied down) is: = VLOOKUP ( $I$4 , Table1 , MATCH ( H5 , Table1 [ #Headers ], 0 ), 0 ) How...
To create a running total in an Excel Table , you can use the INDEX function set up with a s tructured reference . In the example shown, the formula in F5 is: = SUM ( INDEX ([ Total ], 1 ) : [ @ Total ]) When copied down the column, this formula...
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.