CountIf on non contiguous named range

[jakeyjake]

Below is a set of data I am working on. I have named the customer satisfaction range as "CustomerSatisfaction" then i have applied a filter on the cities ie filter by "Melbourne" then i have named the customer satisfaction column with the filter on and called it "MelbourneCustomerSatisfaction". If i do a =CountIf(CustomerSatisfaction,5) named range, all works as expected, however if i do somethign like =CountIf(MelbourneCustomerSatisfaction, 2) it seems to just run is as =CountIf(CustomerSatisfaction,2) rather than apply the melbourne filter to it. Can anyone gimme a hand with this?

[TABLE="width: 933"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Recipt No.[/TD]
[TD]Payment type[/TD]
[TD]Customer's city[/TD]
[TD]Sale price ($/ton)[/TD]
[TD]Delivery time (minutes/ton.km)[/TD]
[TD]Customer satisfaction[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Eftpos[/TD]
[TD]Sydney[/TD]
[TD]345[/TD]
[TD]0.57[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]cheque[/TD]
[TD]Melbourne[/TD]
[TD]444[/TD]
[TD]0.32[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Eftpos[/TD]
[TD]Brisbane[/TD]
[TD]434[/TD]
[TD]0.10[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]cheque[/TD]
[TD]Sydney[/TD]
[TD]382[/TD]
[TD]0.43[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Eftpos[/TD]
[TD]Sydney[/TD]
[TD]412[/TD]
[TD]0.44[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Eftpos[/TD]
[TD]Sydney[/TD]
[TD]451[/TD]
[TD]0.12[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Eftpos[/TD]
[TD]Canberra[/TD]
[TD]358[/TD]
[TD]0.33[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]cash[/TD]
[TD]Brisbane[/TD]
[TD]386[/TD]
[TD]0.67[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]cash[/TD]
[TD]Sydney[/TD]
[TD]407[/TD]
[TD]0.57[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]credit card[/TD]
[TD]Melbourne[/TD]
[TD]345[/TD]
[TD]0.63[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]credit card[/TD]
[TD]Melbourne[/TD]
[TD]390[/TD]
[TD]0.34[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Could you go to name manager and see what the refers to part says for 'MelbourneCustomerSatisfaction'. Is it what you expect?

 

[jakeyjake]

Thanks for your response Steve.

You are right! The named range is not the range that I am after, it chooses random parts of the total column. not sure where excel chooses them from. Is there a way to filter the data and create a range using that filter? Im pretty new to excel so sorry for all the questions and very much appreciate your help.

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you really need to do the filtering?
What version of Excel are you using?

Assuming a reasonably recent version of Excel, here are a couple of options that may be of use.

First, if not done already, turn the data into a formal Excel table (via Insert ribbon tab).

Then you could ...

1. Use a formula like I have in cell H1 to count the Melbourne/2 rows, or

2. Create a Pivot Table (again on the Insert ribbon tab) to get all the City/Satisfaction counts.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Recipt No.	Payment type	Customer's city	Sale price ($/ton)	Delivery time (minutes/ton.km)	Customer satisfaction		0			Count of Customer satisfaction	Customer satisfaction
2	1	Eftpos	Sydney	345	0.57	2					Customer's city	2	3	4	5	6	7	8	Grand Total
3	2	cheque	Melbourne	444	0.32	3					Brisbane	1						1	2
4	3	Eftpos	Brisbane	434	0.1	8					Canberra						1		1
5	4	cheque	Sydney	382	0.43	3					Melbourne		1	1	1				3
6	5	Eftpos	Sydney	412	0.44	5					Sydney	1	1		2	1			5
7	6	Eftpos	Sydney	451	0.12	6					Grand Total	2	2	1	3	1	1	1	11
8	7	Eftpos	Canberra	358	0.33	7
9	8	cash	Brisbane	386	0.67	2
10	9	cash	Sydney	407	0.57	5
11	10	credit card	Melbourne	345	0.63	5
12	11	credit card	Melbourne	390	0.34	4
Counts