The behaviour of SUMPRODUCT() differs between 220.127.116.11 and 18.104.22.168
When the following formula is entered into cell A1 of a new, blank spreadsheet:
The following results are obtained:
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)
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.
(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.
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 ***