Here's what I do on Friday nights!!!

Can anyone explain why the result of this formula is 5:

=SUMPRODUCT((C1:C5<"")/COUNTIF(C1:C5,C1:C5&""))

screencap:

http://img382.imageshack.us/img382/107/uniques1kt6.jpg

I can understand why the result of this one is #DIV/0! (same formula,
different data):

screencap:

http://img376.imageshack.us/img376/558/uniques2nl1.jpg

It seems that COUNTIF is getting "confused" in the first example!

I know that COUNTIF has trouble with *text* true/false, but I can't figure
out what's going on in the first example. If you remove the logical FALSE
then the result is #DIV/0! which I would expect.

To count text true/false:

=COUNTIF(rng,"true*")
=COUNTIF(rng,"false*")

To count logical TRUE/FALSE:

=COUNTIF(rng,true)
=COUNTIF(rng,"true")
=COUNTIF(rng,false)
=COUNTIF(rng,"false")

--
Biff
Microsoft Excel MVP