hi, Sir ! (just wild ideas)
- once you know this...
I know that COUNTIF has trouble with *text* true/false ...
- give to countif a little help and change your formula...
from: =SUMPRODUCT((C1:C5<"")/COUNTIF(C1:C5,C1:C5&""))
to: =SUMPRODUCT((C1:C5<"false")/COUNTIF(C1:C5,C1:C5&""))
(as I said... *just wild ideas*)
hth,
hector.
__ OP __
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