Counting grades in questionaire

[johli]

My problem:
I have the survey data in an excel sheet, one respondent per row and one question per column.

What i would like to do is show the number of grades per question (1-5) based on several criteria (the first 6 questions) for publishing on a company intranet.

Could use a pivottable with reformatting of the data but the number of row would be extremely large (have 25000 respondents and 70 questions)

Have tried using productsum, works but to slow for interactivity.

Would be very grateful for tips of how to do this.
Thinking of creating separate pivot tables for each question but that would mean a lot of tables

/Johan

[Bernie Deitrick]

At the bottom of your first column of grades, use 5 formulas like:

=COUNTIF(B2:B25000,1)
=COUNTIF(B2:B25000,1)
=COUNTIF(B2:B25000,1)
=COUNTIF(B2:B25000,1)
--
HTH,
Bernie
MS Excel MVP


"johli" <[email protected]> wrote in message
news:[email protected]...
>
> My problem:
> I have the survey data in an excel sheet, one respondent per row and
> one question per column.
>
> What i would like to do is show the number of grades per question (1-5)
> based on several criteria (the first 6 questions) for publishing on a
> company intranet.
>
> Could use a pivottable with reformatting of the data but the number of
> row would be extremely large (have 25000 respondents and 70 questions)
>
> Have tried using productsum, works but to slow for interactivity.
>
> Would be very grateful for tips of how to do this.
> Thinking of creating separate pivot tables for each question but that
> would mean a lot of tables
>
> /Johan
>
>
> --
> johli
> ------------------------------------------------------------------------
> johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
> View this thread: http://www.excelforum.com/showthread...hreadid=469120
>

[Bernie Deitrick]

=COUNTIF(B2:B25000,1)
=COUNTIF(B2:B25000,1)

--
HTH,
Bernie
MS Excel MVP


"johli" <[email protected]> wrote in message
news:[email protected]...
>
> My problem:
> I have the survey data in an excel sheet, one respondent per row and
> one question per column.
>
> What i would like to do is show the number of grades per question (1-5)
> based on several criteria (the first 6 questions) for publishing on a
> company intranet.
>
> Could use a pivottable with reformatting of the data but the number of
> row would be extremely large (have 25000 respondents and 70 questions)
>
> Have tried using productsum, works but to slow for interactivity.
>
> Would be very grateful for tips of how to do this.
> Thinking of creating separate pivot tables for each question but that
> would mean a lot of tables
>
> /Johan
>
>
> --
> johli
> ------------------------------------------------------------------------
> johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
> View this thread: http://www.excelforum.com/showthread...hreadid=469120
>

[Bernie Deitrick]

Johan,

Aaargh, I have hit Ctrl-Enter TWICE by accident _twice_ now while pasting. My apologies.

Anyway, at the bottom of column B, enter 5 formulas like

=COUNTIF(B2:B25000,1)
=COUNTIF(B2:B25000,2)
=COUNTIF(B2:B25000,3)
=COUNTIF(B2:B25000,4)
=COUNTIF(B2:B25000,5)

Adjust for the actual range. (These could also go at the top, if you insert 5 rows, and change the
formulas to account for extra data:

=COUNTIF(B7:B65000,1)

Anyway, copy those formulas to the right (for the 70 other columns) and you will get a summary of
the scores for each question.

At least, I think that is what you are looking for. If not, post a SMALL sample of your data table,
and what results you desire to produce.

HTH,
Bernie
MS Excel MVP


"johli" <[email protected]> wrote in message
news:[email protected]...
>
> My problem:
> I have the survey data in an excel sheet, one respondent per row and
> one question per column.
>
> What i would like to do is show the number of grades per question (1-5)
> based on several criteria (the first 6 questions) for publishing on a
> company intranet.
>
> Could use a pivottable with reformatting of the data but the number of
> row would be extremely large (have 25000 respondents and 70 questions)
>
> Have tried using productsum, works but to slow for interactivity.
>
> Would be very grateful for tips of how to do this.
> Thinking of creating separate pivot tables for each question but that
> would mean a lot of tables
>
> /Johan
>
>
> --
> johli
> ------------------------------------------------------------------------
> johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
> View this thread: http://www.excelforum.com/showthread...hreadid=469120
>

[johli]

that or a frequency table would work great but the problem is that I would like to count only those rows that fullfill certain criteries.

example

Company Position Gender Q1 Q2 Q3
A 1 1 2 3 3
A 2 2 4 4 5
B 3 1 4 2 4
B 1 2 5 4 3
C 3 2 1 1 1
A 1 1 2 1 4



Based on the selection of a combination of Company and position for example count the respective number of grades for each question.

like:
Company A
Position 1

Q1 Q2 Q3
1 0 1 0
2 2 0 0
3 0 1 1
4 0 0 1
5 0 0 0


/Johan

[Bernie Deitrick]

Johan,

With your table starting in Row 10, with the Company of interest in cell A1, and the Position of
interest in Cell B1, in cell D1 (Assuming Q1 is in column D), enter

=SUMPRODUCT(($A$10:$A$25000=$A$1)*($B$10:$B$25000=$B$1)*(D$10:D$25000=ROW()))
and copy down to cells D2:D5, then D1:D5 across to line up with any questions that you are
interested in.

HTH,
Bernie
MS Excel MVP


"johli" <[email protected]> wrote in message
news:[email protected]...
>
> that or a frequency table would work great but the problem is that I
> would like to count only those rows that fullfill certain criteries.
>
> example
>
> Company Position Gender Q1 Q2 Q3
> A 1 1 2 3 3
> A 2 2 4 4 5
> B 3 1 4 2 4
> B 1 2 5 4 3
> C 3 2 1 1 1
> A 1 1 2 1 4
>
>
>
> Based on the selection of a combination of Company and position for
> example count the respective number of grades for each question.
>
> like:
> Company A
> Position 1
>
> Q1 Q2 Q3
> 1 0 1 0
> 2 2 0 0
> 3 0 1 1
> 4 0 0 1
> 5 0 0 0
>
>
> /Johan
>
>
> --
> johli
> ------------------------------------------------------------------------
> johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
> View this thread: http://www.excelforum.com/showthread...hreadid=469120
>

[johli]

Tried that but the problem here is performance problems, if this information should be calculated once it would be no problem but it is meant for the user to be able to select different criterias for comparison.

Since sumproduct ends upp in A LOT of matrix multiplications it takes too long time..

/johan