Counting number of occurrences and returning text

[hnt007]

Hi and thanks for helping me out!

I have track titles in column A, and I would like a formula or formulas that will let me know what track titles are present in this column and the number of their occurrences.

Example:
A1: Track Titles
A2: Cool
A3: Here We Go
A4: That Title
A5: Cool
A6: Nice
A7: That Title

So I'd like a formula or formulas that would tell me:
Cool (2)
That Title (2)
Here We Go (1)
Nice (1)

Of course, I won't know the track titles in advance...So I can't say 'search for this specific text' etc...

 

[etaf]

as you are using 365
you could use a UNIQUE() and then countif()

Book1
	A	B	C	D	E
1	Track Titles
2	Cool			Cool	2
3	Here We Go			Here We Go	1
4	That Title			That Title	2
5	Cool			Nice	1
6	Nice
7	That Title
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(A2:A7)
E2:E5	E2	=COUNTIF($A$2:$A$19,D2)
Dynamic array formulas.

or just a countif() in B

Book1
	A	B	C	D	E
1	Track Titles
2	Cool	2		Cool	2
3	Here We Go	1		Here We Go	1
4	That Title	2		That Title	2
5	Cool	2		Nice	1
6	Nice	1
7	That Title	2
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(A2:A7)
E2:E5	E2	=COUNTIF($A$2:$A$19,D2)
B2:B7	B2	=COUNTIF($A$2:$A$10,A2)
Dynamic array formulas.

If you want to modify the title itself and add () with the count, then I think you will need VBA , not my area

 

[etaf]

i have answered your 2nd question using a LET function
which extracts the unique titles and sorts them in frequency order
so that may , if you accept the layout - do both for you

Return the most used title

Hi and thanks for your help! I have a column with track titles and I'm trying to find a formula that will return the most used track title and that would also let me know how many times it was used, like This Is a Cool Title (5) =INDEX(A2:A1000,MODE(MATCH(A2:A1000,A2:A1000,0))) with Ctrl...

www.mrexcel.com

 

[hnt007]

i have answered your 2nd question using a LET function
which extracts the unique titles and sorts them in frequency order
so that may , if you accept the layout - do both for you

Return the most used title

Hi and thanks for your help! I have a column with track titles and I'm trying to find a formula that will return the most used track title and that would also let me know how many times it was used, like This Is a Cool Title (5) =INDEX(A2:A1000,MODE(MATCH(A2:A1000,A2:A1000,0))) with Ctrl...

www.mrexcel.com

Thank you so much!!

 

[etaf]

you are welcome