After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 603671 - SUMIF fails to match on blank cells
SUMIF fails to match on blank cells
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: Analytics
git master
Other All
: Normal normal
: ---
Assigned To: Morten Welinder
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2009-12-03 03:16 UTC by Jeffrey Ross
Modified: 2009-12-03 15:18 UTC
See Also:
GNOME target: ---
GNOME version: ---



Description Jeffrey Ross 2009-12-03 03:16:40 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?
Comment 1 Morten Welinder 2009-12-03 14:55:25 UTC
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.
Comment 2 Morten Welinder 2009-12-03 15:18:19 UTC
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.