count text in cells, dont count duplicates

[egris52788]

i need to count a column of text, but not count duplicates, tried this, but it doesnt work
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Eddie
John
Eddie
jack

answer should be 3 total names

 

[Ron Coderre]

Try this:

=SUMPRODUCT(ISTEXT(A1:A100)*(A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Is that something you can work with?

Edited to include this comment:
BTW....Your formula WILL count all unique items (text and numbers).
If the count of your sample data is 4 and not 3...Are there possibly trailing spaces on any of them?

 

[Domenic]

Does the formula return the #DIV/0! error value? If so, then it's due to an inherent bug...

http://groups.google.com/group/micr...xcel*+author:harlan&rnum=134#c20ec397f954a6ce

Here are a couple of alternatives...

=SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100)))

=SUM(IF(FREQUENCY(IF(A1:A100<>"",MATCH("~"&A1:A100,A1:A100&"",0)),ROW(A1:A100)-ROW(A1)+1),1))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER. Also, the second formula is more efficient.