# LibreOffice Calc -- SUMIFS with Regular Expression

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 close merge delete

Sort by » oldest newest most voted

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.

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.

( 2015-02-27 23:27:30 +0200 )edit

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.

more

## Stats

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

Seen: 1,713 times

Last updated: Feb 27 '15