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

 

[Fluff]

How about

=COUNTIFS(D:D,"*"&E9&"*")

 

[etaf]

how about
=COUNTIF($B$2:$B$5,"*"&A9&"*")

Book3
	A	B	C
1	Name	Region
2	James	Paris, London, Madrid
3	Roger	London, Zurich
4	Thomas	Zurich, Berlin, Paris, Warsaw
5	Anthony	Paris, Madrid
6
7
8	Region	No. of sales persons
9	Paris	3	3
10	London	2	2
11	Madrid	2	2
12	Zurich	2	2
13	Berlin	1	1
14	Warsaw	1	1
Sheet1

Cell Formulas
Range	Formula
C9:C14	C9	=COUNTIF($B$2:$B$5,"*"&A9&"*")

 

[Trevor G]

Not on a PC at the mo. Try using =COUNTIF(range of cells,"*Paris*") as an example

 

[sharshra]

Thanks to etaf & Trever for quick response. Please note that the actual table has thousands of rows with each row containing tens of regions. The table in this thread is just an example with very limited values.

At present, I´m doing this in multiple steps. I would like to simplify this in a single step.
1. Breakdown the values in region to separate rows.
2. Remove duplicates & get unique values from the region field.
3. Count the sales persons in each region.

 

[Fluff]

Thanks to etaf & Trever for quick response.

What about me?

In what way didn't the suggested formulae work?

 

[etaf]

I suspect a better way to do this , sure fluff will have a way

=UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(",",,B2:B5),,",")))
will give a unique range of regions
and then the count as fluff and i posted

probably be able to do both columns with 1 formula

Book3
	A	B	C	D	E	F	G
1			Name	Region
2			James	Paris, London, Madrid
3			Roger	London, Zurich
4			Thomas	Zurich, Berlin, Paris, Warsaw
5			Anthony	Paris, Madrid
6
7
8					Region	No. of sales persons
9					Paris	3	3
10					London	2	2
11					Madrid	2	2
12					Zurich	2	2
13					Berlin	1	1
14					Warsaw	1	1
Sheet1

Cell Formulas
Range	Formula
E9:E14	E9	=UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(",",,D2:D5),,",")))
G9:G14	G9	=COUNTIF($D$2:$D$5,"*"&E9&"*")
Dynamic array formulas.

 

[sharshra]

Oops! Sorry, Fluff. Forgot to include you Thanks to you as well

As I mentioned in my last post, I´m already getting the required result using multiple steps. Countif formula comes in the last step.

The current method involves data manipulation before actually counting. I prefer to avoid these data manipulation steps & use pivot or formula or any better mechanism & get the same result, preferably in single step.

 

[etaf]

maybe somethign like this
does it in 1 step
=LET(a,UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(",",,$D$2:$D$5),,","))),b,COUNTIF($D$2:$D$5,"*"&a&"*"),HSTACK(a,b))
i suspect may be a better to do this

Book3
	A	B	C	D	E	F	G
1			Name	Region
2			James	Paris, London, Madrid
3			Roger	London, Zurich
4			Thomas	Zurich, Berlin, Paris, Warsaw
5			Anthony	Paris, Madrid
6
7
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

Cell Formulas
Range	Formula
E9:F14	E9	=LET(a,UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(",",,D2:D5),,","))),b,COUNTIF($D$2:$D$5,"*"&a&"*"),HSTACK(a,b))
Dynamic array formulas.

 

[Fluff]

Another otion

Fluff.xlsm
	C	D	E	F	G
1
2
3	Name	Region		Region	No. of sales persons
4	James	Paris, London, Madrid		Paris	3
5	Roger	London, Zurich		London	2
6	Thomas	Zurich, Berlin, Paris, Warsaw		Madrid	2
7	Anthony	Paris, Madrid		Zurich	2
8				Berlin	1
9				Warsaw	1
Data

Cell Formulas
Range	Formula
F4:G9	F4	=LET(a,DROP(REDUCE("",D4:D7,LAMBDA(x,y,UNIQUE(VSTACK(x,TRIM(TEXTSPLIT(y,,",")))))),1),HSTACK(a,COUNTIFS(D:D,"*"&a&"*")))
Dynamic array formulas.