Create binary variables based on open ended survey question

[Excel_beginner]

I'm analyzing open ended texts where respondents have stated sports brands they've heard of.
There are 2000 responses and over 35 different brands. I need to calculate percentage (brand awareness) for each brand based on the number of respondents mentioning it. Each respondent can mention up to 4 brands.
I think creating binary columns for each brand and count 0 and 1, where =1 is if the person knows the brand is a good solution.
What I want to achieve is shown in column F and G in the attached screenshot. I've shown for two brands, but I would need to do this for all 35 brands. So basically if for example "Nike" appears in any of the B:E cells, return =1. Otherwise =0.
Can anyone provide a formula for this? Something tells me I need an IFS statement with all 35 brands.
For the sake of simplicity I would like to reference a separate sheet where I keep a column with all brand names. So instead of writing "Adidas", I'd like to reference it as A2, A3 etc.

 

[p45cal]

Change your headers from the likes of Puma(0/1) to just plain Puma, that is, what you're looking for in each row.
Then in cell F2:
=COUNTIF($B2:$E2,F$1)
and copy down (and across).
If there's any likelihood that the same brand is mentioned more than once in a single row then you can use:
=IF(COUNTIF($B2:$E2,F$1)>0,1,0)
to avoid seeing values greater than 1 in the result.

 

[Peter Bartholomew]

Out of curiosity, do you really need the intermediate table of zeros and ones or is it sufficient to go straight to the total counts and percentages?

Counts
= COUNTIFS(data, brand)

Percentages
= COUNTIFS(data, brand) / COUNTIFS(data, "<>")

 

[Peter Bartholomew]

If you have Excel 365, then it is possible to generate the result table and sort it with a formula spilling from just a single cell

= LET(
    counts,  COUNTIFS(data, brand),
    percent, counts / COUNTIFS(data, "<>"),
    SORT(HSTACK(brand, counts, percent), 2,-1)
  )