Counting multiple answers from Forms

[Katarnnl]

Hi all,

I have exported the results of several identical surveys I conducted in Forms and are now trying to combine and analyse the results in Excel. However I get stuck at the responses with multiple answer categories. After using a pivottable on the results, the outcome in Excel show up like this:

Response	Count
answer1;answer2	5
answer1;answer3;other	1
answer1;answer2;answer3	3
other	1
answer3;answer1	2

The issue is that the "other" answer is whatever text the respondent put there. Using SUMIF formulas, I have been able to count the number of answer1, answer2, etc. But I cannot find a way yet to count the "other" answers.

Currently I do not really care what exact text the respondent included, I just want to count the number of respondent who answered anything other than answer1, answer2, etc. So the result I am looking for should look like this:

Response	Count
answer1	11
answer2	8
answer3	6
other	2

Anyone any ideas on this one?

 

[Katarnnl]

That is strange. Not sure how it is calculated, but I got an error here on a list with 7.000 characters, including spaces. Can it depend somehow on system settings perhaps?

 

[Fluff]

What does this return after changing the ranges to match your real data.

=SUM(LEN(REPT(A2:A6,B2:B6)))

 

[Katarnnl]

Ok, that is what I was afried of. If you multiply it with the numbers, you easily go over. If I use your formula on all of the data, it is not even working, I get #VALUE. But if I just adjust the range to a small portion of the data, it already goes over 32,000

 

[Fluff]

How many main answers do you have?

 

[JamesCanale]

If you don't have too many main answers, this could work.

MrExcelPlayground18.xlsx
	A	B	C	D	E
1	Response	Count		Main Answers
2	answer1;answer2;	5		answer1	11
3	answer1;answer3;random;	1		answer2	8
4	answer1;answer2;answer3;	3		answer3	6
5	text;things;	1		other	5
6	answer3;answer1;more random;	2
Sheet12

Cell Formulas
Range	Formula
E2:E4	E2	=LET(x,$A$2:$A$6,y,$B$2:$B$6,SUM(FILTER(y,NOT(ISERR(SEARCH(D2,x))))))
E5	E5	=LET(x,$A$2:$A$6,y,$B$2:$B$6,a,SUBSTITUTE(x,D2,""),aa,SUBSTITUTE(a,D3,""),aaa,SUBSTITUTE(aa,D4,""),b,SUBSTITUTE(aaa," ",""),c,TRIM(SUBSTITUTE(b,";"," ")),d,IF(c<>"",1+LEN(c)-LEN(SUBSTITUTE(c," ","")),0),SUM(d*y))