How to count cells containing specific text only in columns with certain header

Mike423

New Member
Joined
Apr 21, 2023
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Screenshot 2023-04-21 103731.png
    Screenshot 2023-04-21 103731.png
    65 KB · Views: 36

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
In the summary table, the text "No display space available". On of the No reasons.
 
Upvote 0
Ok, how about
Excel Formula:
=SUMPRODUCT((TEXTBEFORE($E$1:$AF$1,":",,,,"")=BT3)*($E$2:$AF$89=BV2))
 
Upvote 0
Solution
Ok, how about
Excel Formula:
=SUMPRODUCT((TEXTBEFORE($E$1:$AF$1,":",,,,"")=BT3)*($E$2:$AF$89=BV2))
Amazing! This looks like it works perfectly. Thanks so much, saved me some time at work for sure.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top