Explanation
First, for context, it’s important to note that you can use COUNTIFS with a regular structured reference like this:
=COUNTIFS(Table1[Swim],"x")
This is a much simpler formula, but you can’t copy it down column H, because the column reference won’t change.
The example on this page therefore is meant to show one way to set up a formula that references a table with a variable column reference.
Working from the inside out, the MATCH function is used to find the position of the column name listed in column G:
MATCH(G5,Table1[#Headers],0)
MATCH uses the value in G5 as lookup value, the headers in Table1 for array, and 0 for match type to force an exact match. The result for G5 is 2, which goes into INDEX as the column number:
INDEX(Table1,0,2,0))
Notice row number has been set to zero, which causes INDEX to return the entire column, which is C5:C13 in this example.
This reference goes into COUNTIFS normally:
=COUNTIFS(C5:C13,"x")
COUNTIFS counts cells that contain “x”, and returns the result, 5 in this case.
When the formula is copied down column H, INDEX and MATCH return the correct column reference to COUNTIFS at each row.
Alternative with INDIRECT
The INDIRECT function can also be used to set up a variable column reference like this:
=COUNTIFS(INDIRECT("Table1["&G5&"]"),"x")
Here, the structured reference is assembled as text, and INDIRECT evaluates the text as a proper cell reference.
Note: INDIRECT is a volatile function and can cause performance problems in larger or more complicated workbooks.
Explanation
In this example, the goal is to create a dynamic reference to an Excel Table in a formula. In other words, create a formula that can refer to an Excel table by name as a variable. The easiest way to do this in Excel is to assemble the reference as a text value using concatenation , then use the INDIRECT function to convert the text reference into a proper Excel reference.
In the example shown, the formulas in L5:L7 behave like these simpler formulas:
=SUM(West[Amount])
=SUM(Central[Amount])
=SUM(East[Amount])
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:
=SUM(INDIRECT(K5&"[Amount]"))
which becomes:
=SUM(INDIRECT("West"&"[Amount]"))
and then:
=SUM(INDIRECT("West[Amount]"))
The INDIRECT function then resolves the text string into a proper structured reference :
=SUM(West[Amount])
And the SUM function returns the final result, 27,500 for the West region.
Note: INDIRECT is a v olatile function and can cause performance issues in larger, more complex workbooks.
