Ask Your Question

LibreOffice Calc -- SUMIFS with Regular Expression [closed]

asked 2015-02-27 22:07:14 +0100

aldab gravatar image

updated 2020-07-28 20:58:03 +0100

Alex Kemp gravatar image

I have a spreadsheet where I'd like to calculate the cumulative count of some quantity in column A where several conditions hold on columns B, C, ... One such condition is a check on the values of column X to see whether they begin with a pattern "PAT" or not. I tried the following but it did not work a̶s̶ ̶S̶U̶M̶I̶F̶S̶ ̶s̶e̶e̶m̶i̶n̶l̶y̶ ̶d̶o̶e̶s̶ ̶n̶o̶t̶ ̶y̶e̶t̶ ̶s̶u̶p̶p̶o̶r̶t̶ ̶r̶e̶g̶u̶l̶a̶r̶ ̶e̶x̶p̶r̶e̶s̶s̶i̶o̶n̶s̶:


Is there any way of doing this in LibreOffice without defining and using auxiliary columns?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-28 20:58:10.521200

2 Answers

Sort by » oldest newest most voted

answered 2015-02-27 22:27:51 +0100

erAck gravatar image

updated 2015-02-27 22:37:02 +0100

Actually the equal operator is default so you don't need to specify it. Replacing "=PAT.*" with "^PAT.*" should also work, but the latter anchors it at the beginning, which makes a difference if "must apply to whole cells" is not enabled.

If both does not work then check that the Tools >> Options >> Calc >> Calculate "General Calculations" option "Enable regular expressions in formulas" is enabled.

edit flag offensive delete link more


Interesting! Just figured out that the reason why mine was not working was due to the fact that the pattern I search for contains '[' which has to be preceded by a backslash in the regular expression.

aldab gravatar imagealdab ( 2015-02-27 23:27:30 +0100 )edit

answered 2015-02-27 22:40:13 +0100

Lupp gravatar image

updated 2015-02-27 22:52:34 +0100

SUMIFS is specified to work with regular expressions if the option is active. This is also implemented and I just tested it again with your formula (reduced to better fitting ranges) with Versions and 4.4.1RC2.

Look for your setting in > 'Tools' > 'Options' ('preferences') > 'LibreOffice Calc' > 'Calculate' > 'Enable regular expressions in formulae'.

As I doubt for some reason in the "Criteria" concept which requires converting numerical values into a text representatiion and back if used in a criterion together with a comparator, I would even prefer:

=SUMPRODUCT($A$1:$A$10 ; $B$1:$B$10 = $E$1 ; $C$1:$C$10 >= POWER($D$1;$D$2) ; ISNUMBER(SEARCH("PAT.*";$F$1:$F$10)))

Sorry: My original post had crossed the other answer.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-02-27 22:07:14 +0100

Seen: 2,364 times

Last updated: Feb 27 '15