count unique records

[spence]

is there a function that can count the number of
different items in a list. in this list of 100, most of
the items are repeated sometimes up to 5 times. i need a
formula that can tell me how many different items are in
the list.

 

[Bob Phillips]

Spence,

This is how

=SUM(1/COUNTIF(A1:A13,A1:A13))

it's an array, so enter with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

One way


SUMPRODUCT((A2:A101<>"")/COUNTIF(A2:A101,A2:A101&""))