On the upgrade to Pop!_OS 22.04 a regex I use in a SUMIF stopped working. Libre version is 7.3.2.2. The option “Enable regular expressions in formulas” is checked, and I’ve tried things like unchecking and rechecking. The formula is SUMIF(Dates,".*/2022",U7:AA7). Dates is a named range. The values in the Dates cells are of the form 03/01/2022, though what is displayed is 03/01/22. Slightly different expressions like 03/.* and .*/01/.* still work (work meaning matches my test date). So I tried “.*/22” and that worked, while “.*/2022” does not. The conclusion I draw is that SUMIF changed to compare against displayed cell value instead of actual cell value. Was that intended? Is there an option to change the behavior back to the way it was? Or have I misunderstood something? If I change the formatting of the date cell to show 03/01/2022 then the original SUMIF works correctly.
Use a formula that works in both cases.
=SUMPRODUCT(YEAR(Dates)=2022;U7:AA7)
@erAck could know if something changed here. However, I’d consider such formula highly unportable, dependent on locale and formatting …
@PKG solution would be the best IMO.
IMHO, this is a serious bug that has been added intentionally “because Excel does the same”. The result of a calculation must not depend on formatting attributes nor locale settings.
t77546.ods (15.6 KB)
Thanks. I don’t want to multiply, only add, so I was surprised this works. Is YEAR(Dates)=2022 returning an array with value 1 for matching cells, like {0,0,0,1,1,1,0,0,0…}?
Noted. It’s for personal use and I don’t allow Windows in the house so portability has never been a concern for me.
I slightly remember someone changed something around matches that introduced bugs that I fixed, but I don’t recall details without digging into git log / bugzilla.
EDIT 2: See comment below.
Uncheck [ ] Precision as shown. It is a few lines below “Enable regular expressions in formulas” option.
Tested with LibreOffice 7.2.7.2 on Windows 10.
EDIT: Don’t work with LibreOffice 7.2.3.2 on Linux 5.3. Later, I will retest on Windows.
It is already unchecked. I toggled it and recalculated just in case, no help.