Structured Table Names for current row

[Calgary_Neil]

I have a structured table called Prod_TechT that is in B82:IX106 that I what to count the Number of "Y"s in every seventh column in IW82..106.
This worked =SUMPRODUCT(N(MOD(COLUMN($B82:IV82)-1,7)=0),N($B82:IV82="Y")), but I want to converted it to the structured names.

i.e. =SUMPRODUCT(N(MOD(COLUMN(@Prod_TechT)-1,7)=0),N(@Prod_TechT="Y"))

where I'm erroring is in isolating Prod_TechT to the current row. The @ errors out and Prod_TechT is a large array.

I not committed to this method but adding or counting every 7th column is important.

Thanks Neil Excel 365

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Are you putting the formula in the table itself, or outside it?
If it's in the table is it after col IV?

 

[Calgary_Neil]

Yes, but it does have to be. (IW comes after IV and before IX). I did that for sort reasons, but if its independent then that makes no differenc,

 

[Fluff]

In that case you can do it like

=SUMPRODUCT(N(MOD(COLUMN(Prod_TechT[[#Headers],[Column1]:[Column256]])-1,7)=0),N(Prod_TechT[@[Column1]:[Column256]]="Y"))

change the column1 & column256 to match the actual column names

 

[Calgary_Neil]

Thanks for that.
That is two areas of structured tables that I don’t like. In this case the column names repeat 36+ times and relative cell references make more sense. The other is a sub Table where a named range is better.
I need time and coffee to think about what works for me.
Again thanks for the answer.
Neil

 

[Fluff]

In this case the column names repeat 36+ times

You cannot have the same column name for multiple columns in a table.