Count of attribute entries for a dynamic column in a file

[Peter Davison]

I’ve got this formula that counts the number of attributes in a column which is okay when I knew which column the attribute heading was in, but now it has become variable.

This is my current formula in a cell –

=SUM(IF(ISTEXT('BCP Raw Product'!$E$2:$E$1000),1/COUNTIF('BCP Raw Product'!$E$2:$E$1000, 'BCP Raw Product'!$E$2:$E$1000),""))+3

I have worked out a formula which matches the attribute name and returns me the column address, but when I try and replace in the original formula it does not like it. I am sure it’s down to brackets and quotes.

="'BCP Raw Product'!$"&CHAR(65-1+MATCH($D42,'BCP Raw Product'!$A$1:$Z$1,0))&"$"&2&":"&"$"&CHAR(65-1+MATCH($D42,'BCP Raw Product'!$A$1:$Z$1,0))&"$"&1000

Returns this –

'BCP Raw Product'!$K$2:$K$1000

I then try to replace - 'BCP Raw Product'!$E$2:$E$1000 In the top formula for the 3 entries with the code above but doesn’t like it?

Would you know what I am doing wrong?

Thanks for your help

 

[Fluff]

How about
=SUM(IF(ISTEXT(INDEX('BCP Raw Product'!$A$1:$Z$1000,,MATCH($D1,'BCP Raw Product'!$A$1:$Z$1,0))),1/COUNTIFS(INDEX('BCP Raw Product'!$A$1:$Z$1000,,MATCH($D1,'BCP Raw Product'!$A$1:$Z$1,0)),INDEX('BCP Raw Product'!$A$1:$Z$1000,,MATCH($D1,'BCP Raw Product'!$A$1:$Z$1,0))),""))+3

 

[Peter Davison]

Thank you I will try this out.

 

[Fluff]

You're welcome