conditional counting of values in Excel 2003

G

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 ?
 
D

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

Guest

You may have to experiment a bit.
Click to expand...


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))}
....
 
Top