conditional counting of values in Excel 2003

[Guest]

Hi everybody

I'm trying to make a statistical analisys of the answers to a
questionnaire like this:

1. Where are you from? (US) (IT) (DE)

2. Look at this picture.
How much do you agree with each of the following statements
(1=totally disagree, ..., 4=totally agree) ?

2.a) It's funny (1) (2) (3) (4) (5)
...
2.z) It's obscene (1) (2) (3) (4) (5)


Answers have been collected like this (in columns,
starting from B):


| A | B | C | D | E | F | G |
----|------------+----+----+----+----+----+----+
1 |Nationality | US | US | IT | US | IT | DE |
----+------------+----+----+----+----+----+----+
2 |It's funny | 4 | 5 | 3 | 5 | 4 | 1 |
----+------------+----+----+----+----+----|----+
3 |It's obscene| 1 | 2 | 3 | 1 | 3 | 5 |
----+------------+----+----+----+----+----|----+



For each question (from row 2 to 99) I need to count how
many interviewed people from US, IT, DE has answered 1,
2, ..., 10.


For row 2 results for US are:


+----+----+----+----+----+
| 1 | 2 | 3 | 4 | 5 |
+----+----+----+----+----+----+
| US | 0 | 0 | 0 | 1 | 2 |
+----+----+----+----+----+----+
| IT | 0 | 0 | 1 | 1 | 0 |
+----+----+----+----+----+----+
| DE | 1 | 0 | 0 | 0 | 0 |
+----+----+----+----+----+----+


For row 3 results for US are:

+----+----+----+----+----+
| 1 | 2 | 3 | 4 | 5 |
+----+----+----+----+----+----+
| US | 2 | 1 | 0 | 0 | 0 |
+----+----+----+----+----+----+
| IT | 0 | 0 | 2 | 0 | 0 |
+----+----+----+----+----+----+
| DE | 1 | 0 | 0 | 0 | 1 |
+----+----+----+----+----+----+


How can I do it using Excel 2003 ?

 

[David McRitchie]

Use a pivot table, a reply to a somewhat similar
question today was in the thread.
http://google.com/[email protected]

Ignore the part about coloring the Pivot Table afterwards.
You can use the Count instead of Sum.
instead of name for rows use the question line,
for columns use choices

You may have to experiment a bit.


<[email protected]> wrote in message news:[email protected]...

 

[Guest]

You may have to experiment a bit.

Thank you very much. I'll look at it.

My temporary solution was this one:


| A | B | C | D | E | F | G |
----|------------+----+----+----+----+----+----+
1 |Nationality | US | US | IT | US | IT | DE |
----+------------+----+----+----+----+----+----+
2 |It's funny | 4 | 5 | 3 | 5 | 4 | 1 |
----+------------+----+----+----+----+----|----+
3 |It's obscene| 1 | 2 | 3 | 1 | 3 | 5 |
----+------------+----+----+----+----+----|----+
4 | | | | | | | |
----+------------+----+----+----+----+----|----+
5 | | | 1 | 2 | 3 | 4 | 5 |
----+------------+----+----+----+----+----|----+
6 | | IT | | | | | |
----+------------+----+----+----+----+----|----+
7 | | DE | | | | | |
----+------------+----+----+----+----+----|----+
8 | | US | | | | | |
----+------------+----+----+----+----+----|----+

C6 is {=SUM(($B$1:$G$1=$B5)*($B2:$G2=C$5))} => CTRL+SHIFT+ENTER
D6 is {=SUM(($B$1:$G$1=$B5)*($B2:$G2=D$5))}
....
C7 is {=SUM(($B$1:$G$1=$B5)*($B3:$G3=C$5))}
....