Greetings. I hope someone can clarify this for me.
Libreoffice 25.2.3.2 running on MA Mini M4 Pro Sequoai 15.5
I have a problem using the Detective trave precedence function with the SUMIF function.
I attach 2 spreadsheets with examples. The widcard function cannot be enabled at the same time as the regular expression function but neither appears to work.
With regular expresions enabled.ods (31.1 KB)
With wildcards enabled.ods (31.2 KB)
Maybe =SUMIF(A2:A10;".*PAYMENT.*";B2:B10)
1 Like
With LibreOffice 25.2.3.2 on Windows 11:
â €
FILE: With regular expresions enabled.ods
Cell B11: empty
Cell B22: 5,121.60
Solution:
as EarnestAl pointed out, the formula in cell C11 should read
=SUMIF(A2:A10;".*PAYMENT.*";B2:B10)
For formula in cell B22 : uncheck Search criteria = and <> must apply to whole cells in Tools ⟶ Options ⟶ LibreOffice Calc ⟶ Calculate.
â €
â €
FILE: With wildcards enabled.ods
Cell C11: 6,863.79
Cell B22: 5,121.60
Solution:
For formula in cell B22 : uncheck Search criteria = and <> must apply to whole cells in Tools ⟶ Options ⟶ LibreOffice Calc ⟶ Calculate.
Thank you both for helping out. Having done some more testing it appears that
a. must have the Search criteria = and <> must apply to whole cells UNCHECKED
b. Must use * as the wild card - note no .full stop if you have “Wild cards enabled option” checked
c. If you use “Enable regular expressions” checked INSTEAD of “Wild cards enabled option” then you must use .* as the wild card - note a .full stop as well as *
However in either case you cannot get the Detective/Trace precedents working correctly so this must be an unrelated issue when using SUMIF function.
Please use the comment bubble, not Suggest a solution when making a comment.
I think that in this case the precedent arrow starts at the start of the range used in the calculation. This is the same as my Excel 2010 does; maybe newer versions of Excel do something different?
Sorry about that. I am rather new on this forum. Thanks for the confirmation that Excel shows the same as mine, although in my case I am using the latest version of Libreofiice. No big deal though.
From List of Regular Expressions
A stop . represents any single character.
The asterisk * means zero or more of the regular expression (.) preceding it
What is your expectation and why? As far as I see, it works OK. It shows the ranges used in the formula, and the arrows point in the correct direction.
Are you expecting the tracing to show you the points from where the values participated in the final sum? you have wrong expectations; this is not the goal of the feature; the goal is to show all the cells, changes in which may affect the results - for instance: if you change a cell in the shown range (e.g., A17 into PAYMENT), the result on the formula in B22 may change.
The two formulas - the one in B22 and the one in B33 - are different; and tracing shows correct results (as designed) for both.
I was expecting the trace for B33 and B22 to show the same, ie the cells that contributed to the total. Thanks for pointing out that trace for B32 is the result of a design feature rather than a bug.
Yes, that’s what I discovered. Thanks for your help.