I am looking to have some summary stats for a table that is generated by a query on another tab, but all the approaches I have taken fail for some reason.

1. Using cell references - COUNTIF(INDEX(Errors!$A$2:$Y$692469,0,MATCH("INCORRECT",Errors!$A$1:$Y$1,0)),TRUE)

With this approach, when the table is generated, the references, even though they are anchored, adjust to the first column that is not part of the generated table.

2. Using structured references =COUNTIF(Errors[INCORRECT],"True")

This only works if the table already exists with that column. If it doesnt exist, excel wont even let me put in the formula. The formula error dialog pops and I cant save it there.

3. Creating a table stub that has the columns I want. For instance, I have a table with headers of ID, INCORRECT with one empty row. When I do it this way, the query ends up creating new columns (INCORRECT2) rendering the structured reference worthless.

So, any suggestions? One complexity is that the the table output does not have completely predictable column names. The ones that I want summary stats on are predictable, however, the other ones are not and in fact there could be a single other column or multiple. So that's annoying. I do know what the names (and quantity) of the other columns are. These are established connections.

CodePudding user response：

So the indirect function was the way to go.

=COUNTIF(INDEX(INDIRECT("Errors!$A$2:$Y$692469"),0,MATCH("INCORRECT",INDIRECT("Errors!$A$1:$Y$1"),0)),TRUE)