Hi. In LibreOffice Calc 6.4.7.2 am trying to sum my incomings and outgoings based on alternative string matches. The screenshot shows a simplified version.
The range ‘description’ covers cells $A$2-$A$4
The range ‘amount’ covers cells $B$2-$B$4
Cell G2 contains the regex that successfully captures the £100 that matches the ‘Revenue’ row in the ‘description’ range.
But as the regex is ‘Revenue|Loan’ I would expect it to capture and sum A2 and A3 and therefore G2 to display £150 but it does not work.
Please can you help me fix this? Thanks.
Always develop relevant sub-expressions before you nest them into a final formula.
In speciific if you try to use a function where you’re (obviously) not familiar with, this is essential.
REGEX() expects 4 arguments, e.g.
Thanks. I am new to this.
I am not sure if it’s a regex issue as I have tried something simpler, using regex itself without putting it into the final formula. It may be how I’m using the range. e.g. this example still doesn’t match the regex across the range A2:A3
with →→Data→→Pivottable…
simple_pivottable.ods (10.0 KB)
or not so obvious with regex:
=SUMPRODUCT(description=IFERROR(REGEX(D2;description; ;1) ;"");amount)
and the easy old:
=SUMIF(description;D2;amount)
with enabled →→Tools→Options→→Calc→→calculate: (x)allow regular Expressions
1 Like
Thank you very much for this. It was what I needed, and even the pivot table suggestion invited me to think outside of regexes at exactly what I’m trying to achieve. I did use your REGEX answer in the end. The ‘easy old’ way did work in my older ODS files created in an older version of Calc, but that that “allow regular Expressions” option is not available in 6.4.7.2.
In 6.4.6.2 it appears under Tools | Options | LibreOffice Calc | Calculate in the Formulas wildcards group as Enable regular expressions in formulas
1 Like
I stand corrected! I really shouldn’t do computing stuff late at at night. Thanks! 
Please don’t attach images (except if the topic is excplicitely about the view) but examples - in this case a little .ods file.
Some comments/hintss and two possible solutions are contained in the attachment:
anotherDemoComparingSUMIFwithForceArrayToSUMPRODUCT.ods (14.8 KB)
1 Like
Thank you for this. Yes, I will upload the ODS file in future - makes it easier for others to recreate the issue as well, rather than trying to copy what I’ve posted in an image. And thanks for your solution. I got several options today - helped develop my knowledge of LibreOffice Calc!