Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count uniques anomaly
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count uniques anomaly
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count uniques anomaly
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","TR UE"},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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count uniques anomaly
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" wrote in message ... 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","TR UE"},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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count uniques anomaly
hi, guys !
as you stated in yur first post... ... COUNTIF has trouble with *text* true/false ... and, yes... ... COUNTIF is getting "confused" in the first example! ... IF you change your (text) false into falso (spanish or whatever other language) you will get the "correct" count of "non-uniques" (within countif function) hth, hector. __ OP __ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Uniques EXCLUDING Some Entries | Excel Worksheet Functions | |||
SUM uniques | Excel Discussion (Misc queries) | |||
Count number of uniques starting with a given letter? | Excel Discussion (Misc queries) | |||
Count Uniques within a list based on value of cell... | Excel Discussion (Misc queries) | |||
How to count uniques of a SUMPRODUCT subset? | Excel Worksheet Functions |