I have an array of cells containing all text, each column containing its own header. The header is the brand name and the text below is the answer of Yes or No, with each cell containing No, having a reason (5 different reasons). I need a total count for each of the 5 reasons for No, for each brand.
Additionally, the header cells have their text broken up by : symbols, I need only the text to the left of the first : symbols to be used to find the counts.
I have another summary table with the brands by row and the reasons for No on the header of each column, the formula would be in each of the data cells in this table.
The formula I need would return the the total counts of each reason for No, by brand. I've provided an image of the table where the info needs pulled from, though only a part of it. The entire table runs from E1 to AF89.
So far I've tried =IF(LEFT($E$1:$AF$1,SEARCH(":",$E$1:$AF$1)-1)=BT3,COUNTIF($E$2:$AF$89,BV2),"") and =COUNTIF(INDEX($E$1:$AF$89,0,MATCH(BT3,LEFT($E$1:$AF$1, SEARCH(":",$E$1:$AF$1)-1),0)),BV2) but they either return the total reason for no for the entire table, not just the one brand, or the answers for only one column instead of all columns containing the brand in header row, respectively.
Any ideas would be very much appreciated. I can answer any clarifying questions as well.
Thanks!
Additionally, the header cells have their text broken up by : symbols, I need only the text to the left of the first : symbols to be used to find the counts.
I have another summary table with the brands by row and the reasons for No on the header of each column, the formula would be in each of the data cells in this table.
The formula I need would return the the total counts of each reason for No, by brand. I've provided an image of the table where the info needs pulled from, though only a part of it. The entire table runs from E1 to AF89.
So far I've tried =IF(LEFT($E$1:$AF$1,SEARCH(":",$E$1:$AF$1)-1)=BT3,COUNTIF($E$2:$AF$89,BV2),"") and =COUNTIF(INDEX($E$1:$AF$89,0,MATCH(BT3,LEFT($E$1:$AF$1, SEARCH(":",$E$1:$AF$1)-1),0)),BV2) but they either return the total reason for no for the entire table, not just the one brand, or the answers for only one column instead of all columns containing the brand in header row, respectively.
Any ideas would be very much appreciated. I can answer any clarifying questions as well.
Thanks!