Count distinct values

[eduzs]

Hi,
This formula works fine to count distinct values in a range:

=SUMPRODUCT(1/COUNTIF(A2:A13,A2:A13))

But it does not works when there's blank cells in the range. There's a solution for this?
Thanks

 

[AlanY]

try

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

 

[Aladin Akyurek]

Better to switch to a faster formula: Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(A2:A13<>"",MATCH(A2:A13,A2:A13,0)),ROW(A2:A13)-ROW(A2)+1),1))

 

[eduzs]

Thanks all