GNOME Bugzilla – Bug 603671
SUMIF fails to match on blank cells
Last modified: 2009-12-03 15:18:19 UTC
If the SUMIF criterion is "" and there are cells in the range that are empty the corresonding value if not included. This is not compatible with Excel which includes such values in its result for the SUMIF function. (I use this feature for bank statement reconciliation - it really is quite useful and although I can work around it this method is quite robust, especially for users who clear cell contents and insert blank lines.) To test for this problem set up A1=1, A2=2, A3=3, B1 blank, B2=8, B3=9, C1=SUMIF(B1:B3,"",A1:A3) Since B1 is blank it should match the criterion of "" and the result of C1 should be A1, which is 1. However it is a disappointing 0. Using C2=SUMIF(B1:B3,D1,A1:A3) and setting D1 to ">8" works correctly (ie C2 result is 3), so that part (which is more important) does work correctly. Tested using Gnumeric Windows 1.9.16 and Linux 1.8.2. I suspect that OpenOffice has the same functionality as Gnumeric (in that I've observed zeros where I expected values) so maybe this was intentional?
We aim to be function compatible. with Excel, except when Excel clearly does the wrong thing. In this case, we should do the same thing.
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.