The SUMIF formula can't calculate the values if the condition is equals to "" (blank field) Please take a look on the attached spreadsheet. The version I'm using is this: BrOffice 3.3.1 OOO330m19 (Build:8) tag libreoffice-3.3.1.2 Thanks!
Created attachment 44792 [details] Spreadsheet that can't calculate blank fields using SUMIF.
[This is an automated message.] This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it started right out as NEW without ever being explicitly confirmed. The bug is changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases. Details on how to test the 3.5.0 beta1 can be found at: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Dear bug submitter! Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs. To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem. Yours! Florian
in 3.6.1 on Fedora still reproducible In comparison, msExcel 2003 produces 50 instead of 0 in this case So, it done not for compatibility with Excel
As there is a discussion in AskLibO (ask.libreoffice.org/en/question/15038/why-does-not-work-as-criterion-for-sumif/?answer=15066#post-id-15066), I made a test. The bug is still there for sumif and for countif. Other related functions are not tested. LibO: Version 3.6.5.2 (Build ID: 5b93205) XP Prof /SP3
Created attachment 76914 [details] Calc file with examples
Created attachment 86149 [details] SUMIF fail if criteria is "*"
Comment on attachment 86149 [details] SUMIF fail if criteria is "*" The bug is also reproducible in LibO: 4.1.0.4 Build ID: 89ea49ddacd9aa532507cbf852f2bb22b1ace28 LibO: 4.2.0.0.alpha0+ Build ID: 2f328ab300175c38ae51025469c1e85b3caf62cd
*** Bug 85985 has been marked as a duplicate of this bug. ***
This bug also exists in LO Calc 4.4. Beta2 Dev
There is one thing that is worse than a crash or a machine slowing down to a crawl: WRONG ANSWERS. Because of this bug, LibreOffice calc cannot replace Excel for inventory types of applications without major changes to existing spreadsheets. Importance: medium & normal may be true for someone who doesn't do any serious spreadsheet work...
For the "*" problem, please open a different bug :) Changing priority of this bug
The wildcard problem exists as separate bug #60345.
Adding Eike Rathke since he's fixed other broken Calc functions.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=01b615687fe0f39c65e0e8290db434db2f1ef8ac Resolves: tdf#35636 implement match on empty cells It will be available in 4.5.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
This implements search criteria "" and "=" to match empty cells in spreadsheet functions SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS and COUNTIFS.
(In reply to Eike Rathke from comment #19) > This implements search criteria "" and "=" to match empty cells in > spreadsheet functions SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS and > COUNTIFS. I know this is not the place for a question but I can't wait for download a master :( Criterion "" will works for both "empty cell" and "empty string" (formula returns a zero-lenght string)? What means criterion "="? Not empty?
Pending review for 4-4 https://gerrit.libreoffice.org/15091 "=" means empty as well, Excel knows that notation. Not empty is "<>" and already worked. And yes, both, empty strings and empty cells, are matched with "".
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c03db327a2904f5b2cef52be03d70b04cb473a52&h=libreoffice-4-4 Resolves: tdf#35636 implement match on empty cells It will be available in 4.4.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/3d79ac71d18566e0bd697eae119d6202dc01b494 tdf#35636: sc: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.