HOW?!? Analyse specific data based on separate criteria

[angeinsydney]

Hi there,
I'm pretty new to this forum but have been using it to help me find answers to excel forumlas for a while. I'm hoping someone on the forum can help.
I have a huge list of survey responses which asks people to identify their location and then rate how important 6 facilities in their area is (rating 1 - 4, 4 being most important).
So the table has cells are similar to this, except that there are about 5000 responses with about 2500 different locations identified
[TABLE="width: 644"]
<tbody>[TR]
[TD]Location
[/TD]
[TD]Location
[/TD]
[TD]Location
[/TD]
[TD]Medical Centres
[/TD]
[TD]Shopping
[/TD]
[TD]Groceries
[/TD]
[TD]Transport
[/TD]
[TD]Schools
[/TD]
[TD]Parks
[/TD]
[/TR]
[TR]
[TD]Shepherd's Bush
[/TD]
[TD]Earl's Court
[/TD]
[TD]Waterloo
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Camden
[/TD]
[TD]Islington
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Hounslow
[/TD]
[TD]Wimbledon
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

I have been able to analyse the average results for the whole dataset by using the following formulas:
=COUNTIF(A2:C4,"camden") Count number of occurrences a location is listed
=ROUND(AVERAGE(D2:D4),2) Average response for medical centres
=COUNTIF(D2:D4,1) Counts number of times '1' comes up, would repeat '2', '3', and '4' to create a pie graph

But what I would really like to do it be able to count each response for each question by location (for example "Camden"). I’ve tried both of these but they don’t seem to be working.
[TABLE="width: 197"]
<tbody>[TR]
[TD]=IF(A2:C4,"camden"),COUNTIF(D2:D4),1
[/TD]
[/TR]
[TR]
[TD]=COUNTIF(D2:D4),1,IF(A2:C4,"camden")
[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help?

 

[TheShaunMichael]

Have you tried using Filter functionality? In the "Data" menu in Excel there is an option named "Filter". By enabling it, you can sort each column by value and/or hide from view any values ("locations" in your case) you're uninterested in. Each column header will appear with a button - just click it to see the options available to you. Does that work?

 

[angeinsydney]

Hi, thanks for your reply. I thought of that however I am concerned that I will miss data which is in another column, for example a2 might equal Waterloo as may c45, if I only sort them I will only really be able to capture responses which are in the first column.

 

[TheShaunMichael]

Gotcha. So what output do you need? Can you walk me through your logic - so row 3 includes "Camden" - then what?

 

[angeinsydney]

Ideally I would want it to be able to tell me how many '1' responses under 'Medical Centres' there are for rows which contain 'Camden', how many '2' responses under 'Medical Centres' there are for rows which contain 'Camden', etc. I would then want to be able to do the same for any other location. Does that make sense/do you need any more info?

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming the particular location cannot occur more than once on a row (or if it does you want it counted multiple times), see if this helps.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Location	Location	Location	Medical Centres	Shopping	Groceries	Transport	Schools	Parks
2	Shepherd's Bush	Earl's Court	Waterloo	4	4	4	4	2	4
3	Camden	Islington		2	3	4	4	3	3
4	Earl's Court	Waterloo	Camden	2	1	1	1	1	1
5	Hounslow	Wimbledon		3	3	3	3	2	3
6
7
8	Location	Camden
9	Type	Medical Centres
10	Value	2
11	Count	2
Sheet1

 

[angeinsydney]

Thanks Peter!

I found another solution: =COUNTIFS(A2:A4,"Camden",D2:D4,1)+COUNTIFS(B2:B4,"Camden",D2:D4,1)+COUNTIFS(C2:C4,"Camden",D2:D4,1)