How to to count unique text in a column- and two specific text

[wmichael]

Hello.

I use this formula to count unique text in a column and it works well:
=ArrayFormula(SUM(IF(FREQUENCY(IF(LEN(L4:L)>0,MATCH(L4:L,L4:L,0),""), IF(LEN(L4:L)>0,MATCH(L4:L,L4:L,0),""))>0,1))) resulting in 6.

But the cells in the column also contain the word "Sea" or "Freedom" followed by a city name. For example "Sea Miami" or "Freedom Michigan" and I want to count the unique text for each Sea and Freedom city as a concatenate formula- For example column L has, there are 4 Sea and 3 Freedom.

Sea Miami
Sea Tampa
Sea Boston
Sea Miami
Sea Miami
Freedom Michigan
Freedom Tampa
Freedom New Jersey
Freedom Tampa

Appreciate your suggestions- thank you, Michael

 

[JoeMo]

Excel Workbook
	A	B	C	D	E
1	Sea Miami		Unique	Unique w/Sea	Unique w/Freedom
2	Sea Tampa		6	3	3
3	Sea Boston
4	Sea Miami
5	Sea Miami
6	Freedom Michigan
7	Freedom Tampa
8	Freedom New Jersey
9	Freedom Tampa
Sheet1

 

[Marcelo Branco]

I can see only 3 unique, not 4, for "Sea"

Try this array formula
=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Sea",L4:L100)),MATCH(L4:L100,L4:L100,0)),ROW(L4:L100)-ROW(L4)+1),1))
Ctrl+Shift+Enter

change "Sea" by "Freedom" to count unique values that contain Freedom

M.

 

[wmichael]

This works - might you know how to use as concatenate

I can see only 3 unique, not 4, for "Sea"

Try this array formula
=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Sea",L4:L100)),MATCH(L4:L100,L4:L100,0)),ROW(L4:L100)-ROW(L4)+1),1)) + =SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Freedom",L4:L100)),MATCH(L4:L100,L4:L100,0)),ROW(L4:L100)-ROW(L4)+1),1))
Ctrl+Shift+Enter

change "Sea" by "Freedom" to count unique values that contain Freedom

M.

Thank you, Michael

 

[wmichael]

Thank you- I was seeking a single column solution. Thanks again, Michael