GNOME Bugzilla – Bug 703770
countif always assumes wildcards on either side of condition
Last modified: 2013-07-10 16:05:55 UTC
[I know it does not belong in a bug report, but first I'd like to tell you: I really like gnumeric, it is a very nice spreadsheet program. Thanks for providing this alternative!] I'd like to count the number of items ending on a string. According to [http://office.microsoft.com/en-001/excel-help/countif-function-HP010069840.aspx], a condition like =COUNTIF(A2:A7,"*es") would count the number of cells ending with the letters "es" in cells A2 through A7. However, in gnumeric it counts all cells containing "es", no matter if =COUNTIF(A2:A7,"*es"), =COUNTIF(A2:A7,"*es*"), or =COUNTIF(A2:A7,"es") is used.
according to https://projects.gnome.org/gnumeric/doc/gnumeric-function-COUNTIF.shtml COUNTIF is Excel compatible
Maybe it is relevant: In OpenOffice the matching behavior can be defined in the options. http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_COUNTIF_function
Do you have a sample sheet? Prefereably stating which cell you want which value in.
This works fine for me with current git version. (Nothing relevant has changed since last stable release.) a1=Jesper a2=apples b1=countif(a1:a2,"es") ---> 0 b2=countif(a1:a2,"*es") ---> 1 b3=countif(a1:a2,"*es*") ---> 2 If you still see a problem, please reopen with a concrete example.
Created attachment 248840 [details] sample screenshot There are strange things going on, see screenshot. C2 is 2 but you (Morten) seem to see 1.
I see wit current git: a1=Jesper a2=apples b1=countif($a$1:$a$2,"es") ---> 0 b2=countif($a$1:$a$2,"*es") ---> 2 b3=countif($a$1:$a$2,"*es*") ---> 2 b4==countif(A1:A2,"*es") ---> 2
This problem has been fixed in our software repository. The fix will go into the next software release. Thank you for your bug report.
Created attachment 248847 [details] gnumeric sheet with countif examples The statement in my bug report >> .. it counts all cells containing "es", no matter if "*es", "*es*", or "es" is used. << is not entirely correct. In fact, in my gnumeric it just acts as if always a "*" is appended to the pattern. An extended example: a1=Jesper a2=apples a3=ester a4=es countif($A$1:$A$4,"es") --> 2 [should be 1] countif($A$1:$A$4,"*es") --> 4 [should be 2] countif($A$1:$A$4,"es*") --> 2 [correct] countif($A$1:$A$4,"*es*") --> 4 [correct] Morten, could you try with the appended sheet?
Great, thank you for the prompt replies.