Bug 35636 - SUMIF does not sum properly accounting for blank cells in the criteria range.
Summary: SUMIF does not sum properly accounting for blank cells in the criteria range.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:4.5.0 target:4.4.3 target:7.1.0
Keywords:
: 85985 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-03-24 12:52 UTC by Tarcisio Gambin
Modified: 2022-05-06 05:54 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet that can't calculate blank fields using SUMIF. (9.13 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-03-24 12:54 UTC, Tarcisio Gambin
Details
Calc file with examples (10.14 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-03-22 14:30 UTC, bugquestcontri
Details
SUMIF fail if criteria is "*" (7.00 KB, application/vnd.ms-excel)
2013-09-19 15:02 UTC, Marco A.
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tarcisio Gambin 2011-03-24 12:52:24 UTC
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!
Comment 1 Tarcisio Gambin 2011-03-24 12:54:01 UTC
Created attachment 44792 [details]
Spreadsheet that can't calculate blank fields using SUMIF.
Comment 2 Björn Michaelsen 2011-12-23 11:46:12 UTC Comment hidden (obsolete)
Comment 3 Florian Reisinger 2012-08-14 13:59:31 UTC Comment hidden (obsolete)
Comment 4 Florian Reisinger 2012-08-14 14:00:42 UTC Comment hidden (obsolete)
Comment 5 Florian Reisinger 2012-08-14 14:05:25 UTC Comment hidden (obsolete)
Comment 6 Florian Reisinger 2012-08-14 14:07:27 UTC Comment hidden (obsolete)
Comment 7 sasha.libreoffice 2012-08-30 10:52:48 UTC
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
Comment 8 bugquestcontri 2013-03-22 14:29:06 UTC
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
Comment 9 bugquestcontri 2013-03-22 14:30:10 UTC
Created attachment 76914 [details]
Calc file with examples
Comment 10 Marco A. 2013-09-19 15:02:50 UTC
Created attachment 86149 [details]
SUMIF fail if criteria is "*"
Comment 11 Marco A. 2013-09-19 15:07:25 UTC
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
Comment 12 raal 2014-11-09 19:09:19 UTC
*** Bug 85985 has been marked as a duplicate of this bug. ***
Comment 13 klsu 2015-01-10 19:09:22 UTC
This bug also exists in LO Calc 4.4. Beta2 Dev
Comment 14 klsu 2015-01-10 19:24:30 UTC
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...
Comment 15 Florian Reisinger 2015-01-12 07:46:46 UTC
For the "*" problem, please open a different bug :)

Changing priority of this bug
Comment 16 klsu 2015-01-12 16:24:40 UTC
The wildcard problem exists as separate bug #60345.
Comment 17 Luke 2015-03-28 01:50:04 UTC
Adding Eike Rathke since he's fixed other broken Calc functions.
Comment 18 Commit Notification 2015-03-31 12:33:44 UTC
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.
Comment 19 Eike Rathke 2015-03-31 12:42:59 UTC
This implements search criteria "" and "=" to match empty cells in spreadsheet functions SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS and COUNTIFS.
Comment 20 GerardF 2015-03-31 13:06:55 UTC
(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?
Comment 21 Eike Rathke 2015-03-31 13:45:56 UTC
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 "".
Comment 22 Commit Notification 2015-04-03 15:36:31 UTC
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.
Comment 23 Commit Notification 2020-07-03 16:06:49 UTC
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.