On Thu, 29 Jan 2009 16:33:02 -0800, 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.


If you are counting blanks, try:

=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5&""))

If you want to ignore blanks, then try:

=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&""))

--ron