Counting grades in questionaire

[johli]

My problem:
I have the survey data in an excel sheet, one respondent per row an
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
company intranet.

Could use a pivottable with reformatting of the data but the number o
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 tha
would mean a lot of tables

/Joha

 

[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)

 

[Bernie Deitrick]

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

 

[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]

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$25000=ROW()))
and copy down to cells D25, then D15 across to line up with any questions that you are
interested in.

HTH,
Bernie
MS Excel MVP

 

[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