Hi
Try this non array formula
=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))

This assume that there're no blank
Cells in the data table

However, if you have a large dataset,
Array formula and this Sumproduct formula
will slow down Excel
Pivot table will be your best solution

HTH

--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"Belle" wrote:

Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.