>The test C1:C5<>"" counts the FALSE as a value, whereas the COUNTIF ignores
>it.
No, COUNTIF is counting it. That's the problem.
Within COUNTIF:
false = FALSE and FALSE&"" = FALSE
But
false <> false and FALSE&"" <> false
So each element of the criteria array is being matched to the logical FALSE
in the range array and being counted.
My actual data didn't contain any logicals but it did contain text
true/false. I used this:
=SUMPRODUCT((A2:A3992<>"")/COUNTIF(A2:A3992,A2:A3992&"*"))
The samples I posted were the results of me tinkering.
Another thing to consider is if the data did contain either true/TRUE and/or
false/FALSE should they be considered equal or not?
Also note that the above formula will not work on numbers
--
Biff
Microsoft Excel MVP
"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%23%(E-Mail Removed)...
> The test C1:C5<>"" counts the FALSE as a value, whereas the COUNTIF
> ignores it.
>
> This works for the first dataset
>
> =SUMPRODUCT((NOT(ISNUMBER(MATCH(C1:C5,{"FALSE","TRUE"},0))))*(C1:C5<>"")/COUNTIF(C1:C5,C1:C5&""))
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>>
>
>
Similar Threads
