Dynamic table to count amount of repeated words

[Reecenorman1996]

Hi all below is a graph i made and the data i made it from only issue is when i update the table the graph doesn't update how can i make this dynamic , thanks in advance

Comparison Stats 2023.xlsx
	D					I	J	K	L	M	N	O	P	Q
2	Property Investment
3	Intermediary
4	Multi Landlord
5	Property Management
6	Intermediary
7	Asset Management
8	Intermediary
9	Developer
10	Developer
11	Property Management
12	Intermediary
13	Auction
14	Solicitor
15	Property Management
16	Intermediary
17	Property Management
18	Multi Landlord
19	Developer
20	Developer
21	Property Management
22	Multi Landlord
23
24	Charity
25	Solicitor
26	Asset Management
Pipeline by client type

 

[duggie33]

You could have the data in a table so if the number of rows increases, the UNIQUE and COUNTIF functions will get the added data.

Book1.xlsx
	A	B	C	D
1	Column1		Property Investment	1
2	Property Investment		Intermediary	5
3	Intermediary		Multi Landlord	3
4	Multi Landlord		Property Management	5
5	Property Management		Asset Management	2
6	Intermediary		Developer	4
7	Asset Management		Auction	1
8	Intermediary		Solicitor	2
9	Developer		Charity	1
10	Developer
11	Property Management
12	Intermediary
13	Auction
14	Solicitor
15	Property Management
16	Intermediary
17	Property Management
18	Multi Landlord
19	Developer
20	Developer
21	Property Management
22	Multi Landlord
23	Charity
24	Solicitor
25	Asset Management
Sheet1

Cell Formulas
Range	Formula
C1:C9	C1	=UNIQUE(Table1[Column1])
D1:D9	D1	=COUNTIF(Table1[Column1],C1#)
Dynamic array formulas.

There are other ways to do this also.

Hope that helps,

Doug

 

[Reecenorman1996]

You could have the data in a table so if the number of rows increases, the UNIQUE and COUNTIF functions will get the added data.

Book1.xlsx
	A	B	C	D
1	Column1		Property Investment	1
2	Property Investment		Intermediary	5
3	Intermediary		Multi Landlord	3
4	Multi Landlord		Property Management	5
5	Property Management		Asset Management	2
6	Intermediary		Developer	4
7	Asset Management		Auction	1
8	Intermediary		Solicitor	2
9	Developer		Charity	1
10	Developer
11	Property Management
12	Intermediary
13	Auction
14	Solicitor
15	Property Management
16	Intermediary
17	Property Management
18	Multi Landlord
19	Developer
20	Developer
21	Property Management
22	Multi Landlord
23	Charity
24	Solicitor
25	Asset Management
Sheet1

Cell Formulas
Range	Formula
C1:C9	C1	=UNIQUE(Table1[Column1])
D1:D9	D1	=COUNTIF(Table1[Column1],C1#)
Dynamic array formulas.

There are other ways to do this also.

Hope that helps,

Doug

Thank you this worked a treat

 

[duggie33]

Happy I could help.

Doug