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

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" wrote in message
...

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

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

--
HTH,
Bernie
MS Excel MVP


"johli" wrote in message
...

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

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" wrote in message
...

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

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

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" wrote in message
...

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

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


--
johli
------------------------------------------------------------------------
johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
View this thread: http://www.excelforum.com/showthread...hreadid=469120