See test case in https://bugs.documentfoundation.org/show_bug.cgi?id=86978#c6
See formulæ in Sheet3.V3:V22.
Load the file with LibreOffice 220.127.116.11-5.fc21 as originally shipped, and observe that these fields have a correct sum of the payments in each tax year.
Load the same file with LibreOffice 18.104.22.168-3.fc21 and observe they all say #VALUE!
The problem was introduced to F22 in a recent update too.
FWIW I've managed to work around this for now by changing from
=SUM(Sheet1.$D$9:$D$500 * (Sheet1.$A$9:$A$500>=$U2) * (Sheet1.$A$9:$A$500<$U3))
... which is probably a better way to do it anyway, but this shouldn't have broken in a stable release.
This changed due to fix for https://bugs.documentfoundation.org/show_bug.cgi?id=42481 . I doubt we want to revert that.
That bug appears to talk about SUMPRODUCT(). It's not clear that they had any intention to change the behaviour of SUM().
(I cannot comment there yet because their new bugzilla is sending mail from a bogus address <firstname.lastname@example.org> to which they do not accept bounces.)
The bug that has been fixed was that arithmetic operators did not propagate errors if used on matrices. That means the change has been in *, not in SUMIFS or any other function.
The root cause of the #VALUE! errors is that in Sheet1.D9:D500 the formulas =IF(AND($A10<>"",ISFORMULA($A10)),1,"") and so on may return a string value that is used in the arithmetic matrix operations and similar to =""*1 or =Sheet1.D20*1 produces an error result, whereas this error was not propagated before. If you change the formula to =IF(AND($A10<>"",ISFORMULA($A10)),1,0) for all then the calculation works as expected.
To suppress the display of a zero value in this case you can use the number format
Note the trailing ;; semicolons.
Those are valid and useful suggestions; thanks.
But my existing version used to work in Fedora 21, and an update broke it.
Is that considered acceptable? I could understand being told when I update from Fedora 21 to 22 that "your sheet was broken; you need to fix it".
But getting that in the middle of a *stable* release is a little more surprising.