Sorting data held across different rows but in same column

Hi

I have some data that contains the results of an online user survey. The survey consisted of a series of multiple choice questions. The answers were logged in a database and have been given to me as an Excel 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, row 5 = user 132's answer to q1)

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

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.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"stu20001" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> I have some data that contains the results of an online user survey.
> The survey consisted of a series of multiple choice questions. The
> answers were logged in a database and have been given to me as an Excel
> 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, row
> 5 = user 132's answer to q1)
>
> If I want to start working out how many times q1 was responded with
> yes, how do i go about it?
>
>
> --
> stu20001
> ------------------------------------------------------------------------
> stu20001's Profile:
> http://www.excelforum.com/member.php...o&userid=37886
> View this thread: http://www.excelforum.com/showthread...hreadid=574281
>