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


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

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.

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.

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.