Count data from separated cells, (not a range)

[trevian3969]

Hi,

I have a file with all the data obtained from 30 questionnaires.

Some questions are related to the service provided by 3 different areas (A1, A2, A3)

The values used are from 1 = excellent service, to 5 = poor service


The data looks like this

' Person 1 Person 2 Person 3 Person 4

' A1 A2 A3 A1 A2 A3 A1 A2 A3 A1 A2 A3

'Question1 5 2 1 2 3 2 5 2 4 5 2

'Question2 3 2 4 1 5 2 5 1 3 2

etc.


How do you read the data: Person 1 answer the question 1 and gives to Area 1 (A1) the value of 5, to A2 the value of 2 and to A3 the value of 1.

The same thing with person 2.....

I need to create some graphics from this data by Area (A1, A2, A3) and by question

The graph must show how many people select 1 or 2 or 3, etc.

In the case of the question 1, the graph must show that 3 people choose 5, and 1 choose 2.

One important thing is that some people are not related with some area, so the cell is left blank.

Im having problems with count how many people choose 1,2,3,4,5 from every area.

i tried to use count if, but I dont have a renge, all the data are separated.


Thanks for your advice.

Trev

 

[keymaster]

You can use COUNTIF to count the area-wise distribution. This is much easier if you data is restructured, but otherwise also possible. I am attaching an example so that you get an idea...


http://img.chandoo.org/playground/survey-response.xlsx


Btw, refer to http://chandoo.org/wp/2010/04/01/incell-panel-chart/ for more ideas.

 

[trevian3969]

Hello Chandoo,

Thank you very much for your advice.

Both (the file, and the link are very helpfull)


Thanks Again.

Trev.