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?

 

[JamesCanale]

MrExcelPlayground18.xlsx
	A	B
1	Response	Count
2	answer1;answer2	5
3	answer1;answer3;other	1
4	answer1;answer2;answer3	3
5	other	1
6	answer3;answer1	2
7
8
9	answer1	11
10	answer2	8
11	answer3	6
12	other	2
Sheet12

Cell Formulas
Range	Formula
A9:B12	A9	=LET(a,DROP(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A2:A6&";",B2:B6)),,";"),-1),b,TRANSPOSE(SORT(UNIQUE(a))),c,--(a=b),d,BYCOL(c,LAMBDA(col,SUM(col))),TRANSPOSE(VSTACK(b,d)))
Dynamic array formulas.

 

[Katarnnl]

MrExcelPlayground18.xlsx
	A	B
1	Response	Count
2	answer1;answer2	5
3	answer1;answer3;other	1
4	answer1;answer2;answer3	3
5	other	1
6	answer3;answer1	2
7
8
9	answer1	11
10	answer2	8
11	answer3	6
12	other	2
Sheet12

Cell Formulas
Range	Formula
A9:B12	A9	=LET(a,DROP(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A2:A6&";",B2:B6)),,";"),-1),b,TRANSPOSE(SORT(UNIQUE(a))),c,--(a=b),d,BYCOL(c,LAMBDA(col,SUM(col))),TRANSPOSE(VSTACK(b,d)))
Dynamic array formulas.

Ok, wow! This is far beyond my area of expertise. But most important, it works.

However, I would like to ask you if you could make 1 change for me. I included an example in my question, but in the real-life case, every answer ends with a semicolon. So the first row of my example would be "answer1;answer2;". Using your formula, it now includes an additional row, counting all the empty spaces after the last semicolon. I could of course just ignore it, but if there is a solution, that would be awesome!

 

[JamesCanale]

The good thing about LET, you can change the output variable to see how the 'build" works.

MrExcelPlayground18.xlsx
	A	B
1	Response	Count
2	answer1;answer2;	5
3	answer1;answer3;other;	1
4	answer1;answer2;answer3;	3
5	other;	1
6	answer3;answer1;	2
7
8
9	answer1	11
10	answer2	8
11	answer3	6
12	other	2
Sheet12

Cell Formulas
Range	Formula
A9:B12	A9	=LET(a,DROP(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A2:A6,B2:B6)),,";"),-1), b,TRANSPOSE(SORT(UNIQUE(a))), c,--(a=b), d,BYCOL(c,LAMBDA(col,SUM(col))), e,TRANSPOSE(VSTACK(b,d)), e)
Dynamic array formulas.

 

[Katarnnl]

MrExcelPlayground18.xlsx
	A	B
1	Response	Count
2	answer1;answer2	5
3	answer1;answer3;other	1
4	answer1;answer2;answer3	3
5	other	1
6	answer3;answer1	2
7
8
9	answer1	11
10	answer2	8
11	answer3	6
12	other	2
Sheet12

Cell Formulas
Range	Formula
A9:B12	A9	=LET(a,DROP(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A2:A6&";",B2:B6)),,";"),-1),b,TRANSPOSE(SORT(UNIQUE(a))),c,--(a=b),d,BYCOL(c,LAMBDA(col,SUM(col))),TRANSPOSE(VSTACK(b,d)))
Dynamic array formulas.

Ok, wait, hold your horses. I was a little bit too fast!

This is a very workable option, but I now notice using it in the real-life case, that it sums up all the unique answers. Which is great, but it should categorize all other answers as 1 item. My fault, my example was not clear on this.

So the answers I have look like this:

Response	Count
answer1;answer2;	5
answer1;answer3;random;	1
answer1;answer2;answer3;	3
text	1
answer3;answer1;more random	2

The result I want to see is:

answer1	11
answer2	8
answer3	6
other	3

So I have a number of options which are always the same (answer1, answer2, etc), and then I have all the random text inserted by respondents when they choose the "other" option.

 

[JamesCanale]

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

Cell Formulas
Range	Formula
A9:B12	A9	=LET(a,DROP(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A2:A6,B2:B6)),,";"),-1), z,D2:D4, aa,IF(ISNUMBER(XMATCH(a,z,0)),a,"other"), b,TRANSPOSE(SORT(UNIQUE(aa))), c,--(aa=b), d,BYCOL(c,LAMBDA(col,SUM(col))), e,TRANSPOSE(VSTACK(b,d)), e)
Dynamic array formulas.

 

[Katarnnl]

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

Cell Formulas
Range	Formula
A9:B12	A9	=LET(a,DROP(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A2:A6,B2:B6)),,";"),-1), z,D2:D4, aa,IF(ISNUMBER(XMATCH(a,z,0)),a,"other"), b,TRANSPOSE(SORT(UNIQUE(aa))), c,--(aa=b), d,BYCOL(c,LAMBDA(col,SUM(col))), e,TRANSPOSE(VSTACK(b,d)), e)
Dynamic array formulas.

This is really perfect. Thank you so much!

 

[JamesCanale]

You're welcome. I like it when some of the real all-stars come in and do the same thing with half the formula. It's amazing what some of them can do. I learn tons from that when I can unpack what they are up to, and learn all kinds of uses for usually the new formulas. I learned the practical use of LAMBDAs that way.

 

[Katarnnl]

You're welcome. I like it when some of the real all-stars come in and do the same thing with half the formula. It's amazing what some of them can do. I learn tons from that when I can unpack what they are up to, and learn all kinds of uses for usually the new formulas. I learned the practical use of LAMBDAs that way.

It seems I'm running into a new issue. I get a #CALC! error, telling me: TEXTJOIN: Text too long

I tried to shorten several of the answer categories, but I still get the error. Any ideas how I can solve this one?

 

[JamesCanale]

I think a textjoin can only be about 32,000 characters long. Is your list much bigger than that?