counting unique items with annexs

[Guest]

hi all,
seeking help please
the column A1:A100 contains items like in cell A1 facil.1
A2
beauty.2
A3 facil.
A4
facil.3
A5
beauty.4
and so on...
Looking for a solution where on counting items will yield 2 (for "facil"
and "beauty") and not 5 as each entry is unique, as the flowing formula do:
=sum(if(frequency(match(A1:A100,A1:A100,0)match(A1:A100,A1:A100,0))>0,1))
many thanks for any help.

 

[Bernie Deitrick]

ExcelFan,

Use a helper column of formulas, with the formula

=LEFT(A1,FIND(".",A1,1)-1)

Copy down to match your data, and use the Count unique items formula on that
column.

HTH,
Bernie
MS Excel MVP