Count Distinct

[lljones]

Hi--
I'm trying to count distinct text values in a range. I found this formula on another help site

[FONT=&quot]=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))


.... but it gives me a self-referencing error and returns 0 (My formula is placed in column B).

Thanks in advance.[/FONT]

 

[bosco_yip]

This is an array formula, press Ctrl+Shift+Enter 3 keystrokes together instead of just Enter :

=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))

Or,

try this regular formula :

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

Regards

 

[lljones]

Thanks!