Thread: Count Different Values in a column
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Suranthe de Silva Suranthe de Silva is offline
external usenet poster
  
Posts: 1
Default Count Different Values in a column
If the values are in the range A1:A10 then

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

The first part gives 1 for each cell that is not empty. Then divides it
with how many instances there is of this value.
Summing that gives you the "unique count".

The &"" is to avoid 0 and #DIV/0 when a cell is empty.

Ref: http://www.mrexcel.com/archive2/63500/73502.htm

Happy Programming!
- Suranthe

*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote