[Solved] Is SUMIFS supposed handle Regular Expressions?

[samnmn]

Looks like it does not or maybe I need help with my regular expression. I used "1.." looking for the first digit of a 3 position code to be 1. It did not work. I then added a column to my worksheet which had =LEFT(An,1) for each row in my range. I then changed the SUMIFS to range over the new column and that criteria to "1" and got expected results. So, what did I do wrong in my Regular Expression?

[Lupp]

Hi and welcome first of all.

Editing (subsequent to the lines below and to my other post in this thread): Sorry! I missed to read "1.." as the regular expression you used. If your "3-digit-codes" are of type text and the respective cells are assured to contain each such a code and nothing else, your RegEx should work. I just tested and found it didn't. It's a bug.
Workarounds: Use "^1..$" or "1[0-9]{2}". Second suggestion preferable, imo. It should be equivalent to the RegEx suggested by @robleyd. Sorry for my sloppy reading!

SUMIF() and SUMIFS() are specified to support RegEx if the repective option is set.

Unfortunately you did neither post the formula that didn't work for you as expected or (much better) attach a reduced example file. Therefore to suggest a solution is some stabbing in the dark. Anyway I should remind you of the fact that regular expressions in formulae are only evaluated if the respective option is enabled under 'Tools' > 'Options' > 'OpenOffice Cakc' > 'Calculate'.

[robleyd]

Assuming you have enabled regex in formulae, it should - see this article in the Wiki

Your regex looks a bit wrong to me, but my regex-fu is weak I think that will match 1 followed by any character, twice. Maybe 1[0-9][0-9] ?

[Lupp]

Editing subsequent to the below lines: Sorry! See my first post edited, please. I did not delete the bad parts of my posts to avoid misleading history.

Your regex looks a bit wrong to me, ...

"Your" seems to address @samnmn here, but when I created my answer I didn't find any RegEx in the Original Question, and at this moment there is none there as far as I can see. Would you (@robleyd) mind to post the Regex you found?

@samnmn: Though I am rather experienced with RegEx I did not suggest a SUMIF() or SUMIFS() formula or some surrogate based on RegEx because I couldn'f find sufficient information about the composition (syntax) of the entries to check against the 'Criteria'. If these entries actually are three digits and nothing else, you are right to simply check for the first character. This will at least be more efficient as compared with RegEx usage - and easier to understand / maintain.
However, if the type of the entries is 'Number' and in case of a leading zero the three-digit-display is only achieved by the 'Numbers' format of the cells, the character returned by LEFT(A2; 1) (e.g.; Semicolon as the parameter delimiter, not a comma!) will be the second digit in your numbering.

For future questions: If something "...did not work" never miss to also tell in what way the result differed from your expectations. If you got an Err:508 or a #NAME? error message most probably the wrong delimiter was the cause, and we are only wasting time here.

[Villeroy]

1.. matches 1 followed by any other 2 characters.

A sequence of 3 digits starting with 1 is matched by
1[:digit:][:digit:]
or with a quantifier {2}
1[:digit:]{2}
^1[:digit:]{2}$ matches a sequence of 3 digits starting with 1 between the start ^ and the end $; in other words: the 3 digits are the only characters.

^1[:digit:]{2}$
and
1[:digit:]{2}
are equivalent if the "match whole cell" option is checked together with the regex option in Tools>Options ... Calc>Calculation.

[samnmn]

Thanks for help from all. I used "1[0-9]{2}" and problems went away. The range is over numeric entries representing account numbers. I suppose I should convert them to character strings as they are nut used as numbers.

Who enters bug report?

Thanks again,
Sam

[Lupp]

I suppose I should convert them to character strings as they are not used as numbers.

Good idea. Necessary if codes with leading zero can occur.

Who enters bug report?

You may do that. However, AOO doesn't seem to be highly engaged in bug fixing. LibO Calc had also inherited this bug, but as I just tested it was fixed (at latest) with LibO V 4.1.5.3 of 2014-02-07. (I dont't have a 4.0 version at hand. In V3.6.5 the bug was still living.)

Anyway the restricton of second and third character to decimal digits is not just a workaround, but also preferable semantically.