GNOME Bugzilla – Bug 417559
COUNTIF problems
Last modified: 2009-12-09 22:19:37 UTC
countif(range,"value") generally works the same as countif(range,"=value") even if the value is a string. However countif(range,"") does not usually count blank cells, and neither does countif(range,"=") or countif(range,"=''"). I say "usually" because I have seen countif(range,"") work once with a Gnumeric sheet (in one snapshot in the 1.7.x series) but it failed when I imported the .gnumeric file into EditGrid (which is currently based on gnumeric-1.6.4), which alerted me to the problem. I have since updated from SVN and haven't been able to get the latest snapshot to support it. Note that countif(range," ") works as expected (counting the number of cells containing a single space). I don't have access to an Excel install right now to check on the behavior there. I know that Excel has weird inconsistent logic for ranges if they are beyond the end of content-containing cells in the sheet, but this countif usage seems broken even when the range is a set of formulas like: if(cond,"x","") Perhaps regardless of Excel's logic, A justification for fixing this is that countif(range,"") seems to always return zero regardless of what is or isn't in the range, making it a pretty useless call. Being able to count blanks is pretty important. I am aware of bug 404095, but it seems to me that this can be fixed within the current framework without adding a new function.
Word wrap split one of the lines: Note that countif(range," ") works as expected (counting the number of cells containing a single space).
I really need concrete samples that you think are not evaluating right.
In a blank sheet, into b1 type: =countif(a1:a5,"") Observe the result is 0. I would expect it to be 5. Typing things into some but not all the cells a1:a5 doesn't change the answer. Also if you fill a1:a5 with =if(mod(row(),2)=0,"x",""), it still doesn't give the expected result of 2.
Oops, that last comment should say the expected result is 3 not 2.
Ok, I see. We don't seem to have a formal description of what "criteria" is supposed to be. We seem to ignore blanks. #warning 2006/May/31 Why do we not filter non-existent as a flag, rather than checking for NULL in cb_countif I don't see code to handle criteria like "xxx?yy*z" What about strings that look like numbers? (Does "10" match "10.0"?)
> In a blank sheet, into b1 type: =countif(a1:a5,"") > Observe the result is 0. I would expect it to be 5. In current git the result is 5 > Also if you fill a1:a5 with =if(mod(row(),2)=0,"x",""), it still doesn't give > the expected result of 3. In current git the result is 3. So I think the initial report can be considered fixed. (Morten's thoughts in comment 5 would really first need to become some behaviour expectations.
Thanks, created Bug 604214 to deal with Morten's thoughts in comment 5 so they don't get lost.