GNOME Bugzilla – Bug 123325
Excel incompatibility: COUNTIF conditionals
Last modified: 2004-12-22 21:47:04 UTC
COUNTIF(range,"<>0") doesn't count cells with formulae in that return "", in Gnumeric (i.e. "" is treated as 0), but does count them (i.e. "" is not treated as 0) in Excel. (I imagine SUMIF etc. have the same problem.) This just broke compatibility on a sheet that I created.
@#%$#$ dick heads XL is inconsistent. If the entire range is empty and outside the region known to have content it is ignored. Hence countif (D1:D5,"<>1") == 0 if there is nothing beyond column C and == 5 if there is any value <>1 in D1:D5 I've patched it, but will need to test things more before committing.
I think it's more complicated than that. I was counting a region with formulae returning "" or a number, depending on some condition. It was definitely inside the defined region. I'm reopening so you notice this corner-case.
understood. My comment was that gnumeric's approach of ignoring empties was consistent where as the XL approach of sometimes counting empties for '<>' was not. I've patched things so that we mostly match their semantics now, although it is possible to get different results depending on how tight the definition of the active region is.