Ask Your Question
0

LibreOffice Calc -- SUMIFS with Regular Expression

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

aldab gravatar image

updated 2015-02-27 23:30:05 +0200

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̶:

SUMIFS($A$1:$A$100,$B$1:$B$100,$Z$1,$C$1:$C$100,">="&POWER($Z$2,$Z$3),$X$1:$X$100,"=PAT.*")

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

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

erAck gravatar image

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

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

Comments

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 +0200 )edit
0

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

Lupp gravatar image

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

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 4.3.5.2 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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 1,713 times

Last updated: Feb 27 '15