Reference table field using Concatenate

Question

I'm trying to use this formula to capture the field names in a table on a different sheet.  I'm am trying to get the percentage of "True" in each field in the table tblRes - I'm being lazy in that I don't want to have to rewrite each formula for each field.

=(COUNTIFS(CONCATENATE("tblRes["&b9&"]"),"True",tblRes[Month],C$8))/(COUNTIFS(CONCATENATE("tblRes["&B9&"]"),">""",tblRes[Month],C$8))

Any help would be appriceated.

Thank you.

Jules

Answer 1

I'm trying to use this formula to capture the field names in a table on a different sheet.  I'm am trying to get the percentage of "True" in each field in the table tblRes - I'm being lazy in that I don't want to have to rewrite each formula for each field.

 

 

 

=(COUNTIFS(CONCATENATE("tblRes["&b9&"]"),"True",tblRes[Month],C$8))/(COUNTIFS(CONCATENATE("tblRes["&B9&"]"),">""",tblRes[Month],C$8))

 

Any help would be appriceated.

 

Thank you.

Jules

Hi,

The function you want is INDIRECT and not CONCATENATE. Also I don't understand this underlined bit of your formula:-

(CONCATENATE("tblRes["&B9&"]"),">""",tblRes[Month],C$8))

I think you mean here <>""

Try this:-

Edited formula:-

=(COUNTIFS(INDIRECT("tblRes["&B9&"]"),TRUE,tblres[Month],C$8))/(COUNTIFS(INDIRECT("tblRes["&B9&"]"),"<>",tblres[Month],C$8))

Note also I removed the quotes from TRUE on the assumption these are BOOLEAN TRUE in your table, If they're TEXT you may have to put the quotes back.

Answer 2

I'm trying to use this formula to capture the field names in a table on a different sheet.  I'm am trying to get the percentage of "True" in each field in the table tblRes - I'm being lazy in that I don't want to have to rewrite each formula for each field.

 

 

 

=(COUNTIFS(CONCATENATE("tblRes["&b9&"]"),"True",tblRes[Month],C$8))/(COUNTIFS(CONCATENATE("tblRes["&B9&"]"),">""",tblRes[Month],C$8))

 

Any help would be appriceated.

 

Thank you.

Jules

Hi,

The function you want is INDIRECT and not CONCATENATE. Also I don't understand this underlined bit of your formula:-

(CONCATENATE("tblRes["&B9&"]"),">""",tblRes[Month],C$8))

I think you mean here <>""

Try this:-

Edited formula:-

=(COUNTIFS(INDIRECT("tblRes["&B9&"]"),TRUE,tblres[Month],C$8))/(COUNTIFS(INDIRECT("tblRes["&B9&"]"),"<>",tblres[Month],C$8))

Note also I removed the quotes from TRUE on the assumption these are BOOLEAN TRUE in your table, If they're TEXT you may have to put the quotes back.

Yes! That's exactly what I needed - totally forgot about INDIRECT.

Thank you!

Answer 3

I am having a little trouble understanding what it is you wish to compute. But her is my offering.

I have a table of data and some results.

The result count how many times [boolean1] and [boolean2] are both TRUE and [MONTH] euals the value in column A.

To get  [boolean1] OR [boolean2] are TRUE change the * to + in the SUMPRODUCT

best wishes