Excel_beginner
New Member
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.
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.
Attachments
Last edited: