Counting problem

[sharshra]

I have a table with one of the field having comma separated values. I want to count for each value in that field how many items are there. I´m sure there must be a simple way to do this.

For example, consider the table below. It has name of sales person & regions they cover. I would like to have a formula to list down how many sales persons are there for each region. Note that the actual table has thousands of values. I have taken just 4 rows for illustration.

Table -

Book1
	C	D
3	Name	Region
4	James	Paris, London, Madrid
5	Roger	London, Zurich
6	Thomas	Zurich, Berlin, Paris, Warsaw
7	Anthony	Paris, Madrid
Sheet1

Output required -

Book1
	E	F
8	Region	No. of sales persons
9	Paris	3
10	London	2
11	Madrid	2
12	Zurich	2
13	Berlin	1
14	Warsaw	1
Sheet1

 

[sharshra]

Thank you very much, @Fluff You made my day It works perfectly as expected.

I still have problem with running this formula in another worksheet, though correct sheet name is given. But that´s not a major concern & I can live with it for now. However, would like to fix to make it a complete solution. Will try it later.

Marking Fluff´s post #20 as the solution.

@Cubist , Thanks for your VBA code. I´m sure your VBA code will be useful & will be using it too for some other requirements.

 

[Fluff]

I still have problem with running this formula in another worksheet, though correct sheet name is given

Make sure the sheet name does not have any leading/trailing spaces.
Also is the table a proper table, or just a normal range?