LibreOffice Calc -- SUMIFS with Regular Expression

[aldab]

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?

[erAck]

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.

[aldab]

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.

[Lupp]

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.