Sorting data held across different rows but in same column

[stu20001]

Hi

I have some data that contains the results of an online user survey
The survey consisted of a series of multiple choice questions. Th
answers were logged in a database and have been given to me as an Exce
sheet.

The data is as follows

Col 1: User ID
Col 2: Responses

So some sample data would look like this:

131 Yes
131 Maybe
131 No
131 Yes
132 No
132 Yes
132 Maybe
132
133 Yes
133 No.....

..where each row contains the respondants answers to each question

(i.e. row 1 = user 131's answer to q1, row 2 = 131's answer to q 2, ro
5 = user 132's answer to q1)

If I want to start working out how many times q1 was responded wit
yes, how do i go about it

 

[RagDyer]

One approach would be to add a 'helper' column where the questions can be
individually numbered.

With user ID in Column A and responses in Column B, say in C1 you enter this
formula:

=COUNTIF($A$1:A1,A1)

*Double* click the fill handle of C1, which will *automatically* copy the
formula down Column C, as far as there is data in Column B.

Now that you have the answers numbered, enter this formula in say D1, and
copy down to D4:

=SUMPRODUCT(($C$1:$C$100=C1)*($B$1:$B$100=$E$1))

Where the *answer* that you're looking to count is entered in E1.

You can continue to copy the Sumproduct formula down Column D, and *change*
the 'answer' cell to say E5, and then E9, and so on, and enter another
variable in each, to have a running total of all your answers.