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
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

<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>
</tbody>

Sheet2​

Array Formulas Cell 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))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Copy the formula in K2 through Q3

 

[Aryatect]

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