# Thread: Combining survey results in excel Thanks: 0 Likes: 0

1. ## Combining survey results in excel

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.

2. ## Re: Combining survey results in excel

Hi, are you looking for something like this ?

ABCDEFGHIJKLMNOPQ
1Question Name of Responder Company Location Dept 1 Dept 2 Dept 3 Dept 4Counts Name of Responder Company Location Dept 1 Dept 2 Dept 3 Dept 4
2Q1AABCNY, USAYesNoYesYesQ12221111
3Q2AABCNY, USAYes:centralNo:DecentralNo: DecentralNo:centralQ22222112
4Q1BDEFEUYesNoYesYes
5Q2CDEFEUYesNo:DecentralNo: DecentralNo:Decentral

Sheet2

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

Copy the formula in K2 through Q3

3. ## Re: Combining survey results in excel

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

Originally Posted by Aryatect
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
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))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Copy the formula in K2 through Q3