Combining survey results in excel

[ab1275]

Is there a way to combine results in excel? Survey was sent to ppl in different countries.

Survey has dropdowns for each question. Responder will answer all questions.

Format of the survey


Question - Name of Responder - Company - Location - Dept 1 - Dept 2 - Dept 3 - Dept 4

Q1 - A - ABC - NY, USA - Yes - No - Yes - Yes
Q2 - A - ABC - NY, USA - Yes:central - No:Decentral - No: Decentral - No:Decentral
Q1 - B - DEF - EU - Yes - No - Yes - Yes
Q2 - B - DEF - EU - Yes:central - No:Decentral - No: Decentral - No:Decentral

How do i combine the results such that i can get a count of different responses? Basically be able to slice & dice the results in different ways possible.

 

[Aryatect]

Hi, are you looking for something like this ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Question	Name of Responder	Company	Location	Dept 1	Dept 2	Dept 3	Dept 4		Counts	Name of Responder	Company	Location	Dept 1	Dept 2	Dept 3	Dept 4
2	Q1	A	ABC	NY, USA	Yes	No	Yes	Yes		Q1	2	2	2	1	1	1	1
3	Q2	A	ABC	NY, USA	Yes:central	No:Decentral	No: Decentral	No:central		Q2	2	2	2	2	1	1	2
4	Q1	B	DEF	EU	Yes	No	Yes	Yes
5	Q2	C	DEF	EU	Yes	No:Decentral	No: Decentral	No:Decentral
Sheet2

Cell Formulas
Range	Formula
K2	{=SUM(IF(FREQUENCY(IF($A$2:$A$5=$J2,MATCH(B$2:B$5,B$2:B$5,0)),ROW($E$2:$E$5)-ROW($E$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the formula in K2 through Q3

 

[ab1275]

How could you insert a sheet in this forum? I could attach a sample for ref.

Hi, are you looking for something like this ?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Question	Name of Responder	Company	Location	Dept 1	Dept 2	Dept 3	Dept 4	Counts	Name of Responder	Company	Location	Dept 1	Dept 2	Dept 3	Dept 4
Q1	A	ABC	NY, USA	Yes	No	Yes	Yes	Q1
Q2	A	ABC	NY, USA	Yes:central	No:Decentral	No: Decentral	No:central	Q2
Q1	B	DEF	EU	Yes	No	Yes	Yes
Q2	C	DEF	EU	Yes	No:Decentral	No: Decentral	No:Decentral

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($A$2:$A$5=$J2,MATCH(B$2:B$5,B$2:B$5,0)),ROW($E$2:$E$5)-ROW($E$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Copy the formula in K2 through Q3

 

[Aryatect]

Place your sheet on dropbox or google drive or like and provide the link.