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?

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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