Bug 74547 - SUMPRODUCT behaviour change between and
Summary: SUMPRODUCT behaviour change between and
Status: RESOLVED DUPLICATE of bug 74479
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other macOS (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2014-02-05 07:40 UTC by Matthew Francis
Modified: 2014-02-08 23:14 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Matthew Francis 2014-02-05 07:40:15 UTC
The behaviour of SUMPRODUCT() differs between and

When the following formula is entered into cell A1 of a new, blank spreadsheet:


The following results are obtained: 0 10

The right hand argument ($C$1:$C$10="") should be true (=1) across the range of empty cells in column C, and previously an empty cell (column B) has always evaluated to "0" in this context, so each pair of column B and column C cells should evaluate to 0*1 = 0, not 1.

(This formula has been simplified from one in actual use in a much larger spreadsheet)
Comment 1 m.a.riosv 2014-02-05 09:12:39 UTC
Hi Matthew, thanks for reporting.

There is an option in:
Menu/Tools/LibreOffice calc/Formula/Detailed calculation settings - Custom - Details - Treat empty string as zero.

Please verify if setting up to True solves your issue.
Comment 2 Matthew Francis 2014-02-05 09:39:47 UTC
(In reply to comment #1)

Thanks for the suggestion.
I'm afraid that option doesn't appear to make any difference to either the original spreadsheet or the simplified formula I reported. If it's supposed to then perhaps that should be looked at further.

I found a different workaround for the expression I need, which is to wrap the first argument with N(...) to cast the column values explicitly to numbers, which includes making "" equal to 0 (all the values in question are numbers or "").
If ""=1 is going to be the default in this context in future, I think it will make sense to stick with this, and adding the extra function doesn't appear to diminish the otherwise massive (and highly welcome) speedup I've seen with 4.2.
Comment 3 m.a.riosv 2014-02-08 23:14:19 UTC
Seems a duplicate of bug 74479.

Please if you are not agree reopen it.

*** This bug has been marked as a duplicate of bug 74479 ***