GNOME Bugzilla – Bug 604214
Criteria match failure for COUNTIF (and others?)
Last modified: 2009-12-11 14:57:05 UTC
The "criteria" field of COUNTIF (and other related functions, I think there are a few of them?) needs to be formally defined and documented. In https://bugzilla.gnome.org/show_bug.cgi?id=417559#c5 , Morten Welinder wrote: > Ok, I see. We don't seem to have a formal description of what "criteria" > is supposed to be. > > [...] > > I don't see code to handle criteria like "xxx?yy*z" > > What about strings that look like numbers? (Does "10" match "10.0"?) Andreas Guzelow wrote: > Morten's thoughts in comment 5 would really first need to become some > behaviour expectations. Personally I think that at least in the case of strings that look like numbers, everything should be converted to numbers where possible, as it is most likely to be what users expect. (I can only assume that is what Excel does?) Ideally it would be really great if the criteria part could be extended with some sort of optional lambda-expression equivalent, because the Excel criteria syntax is so awful... but that's a different matter
We have code for "xxx?yy*z" nowadays. Blanks-handling was fixed recently. We match as numbers when possible. -->Closing. I don't know of a case where we don't do the right thing. Re-open if that materializes.
I think this might be a case of a problem: In A1:A5 enter the following: '10 10 '10.0 10.0 10 <- format as Text Then in B1 enter =COUNTIF(A1:A5,"10"). You get 4 not 5. The culprit is: '10.0
Note that we do have a "formal" description inthe ECMA standard: The criteria "Designates the cells to be counted. In the case of text, selection-criteria can consist of any comparison operator followed by the operand against which each cell's value is to be compared. selection-criteria can include one or more wildcard characters, question mark (?) and asterisk (*). A question mark matches any single character; an asterisk matches any sequence of characters. To search for a question mark, asterisk, or tilde character, prefix that character with a tilde (~)."
> Then in B1 enter =COUNTIF(A1:A5,"10"). You get 4 not 5. Confirmed. Note, that "=10" works as does 10 as a number. We are doing that match as a string here. It would appear that XL treats it as an equality test. The real problem is, of course, that the whole thing has crazy semantics. Not much we can do about that, though.
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.
Note, that this is so messed up that searching for "TRUE" (as a string) will match TRUE (the boolean) but not "TRUE" (the string). Welcome to the world of XL semantics. I don't think there is a way to search for "TRUE" as a string! "~TRUE" does not work, for example. We are not currently matching this perfectly. I'll have to investigate.
That is now fixed too. (And test cases have been added to make it harder to mess this up in the future.)