sumifs() with wildcard question

users
#1

A B
1 aa 1
2 ab 2
3 bb 10
4 bc 10
5 a* 0
Formula in cell B5 =SUMIFS(B1:B4,A1:A4,A5) should return 3 not 0.

Tool > Options > LibreOffice Calc > Calculate
Enable regular expression in formulas is *off* (I don't want regexp for
this worksheet).

I though there was a setting that controls how the * wildcard is
interpreted in Calc.
Can someone please help?

#2

If you need this sort of thing, it may be that you *do* need regular expressions. Surely you can design other parts of your spreadsheet so that they will work correctly even with regular expressions allowed in such contexts? Remember that you can use the backslash to escape characters that would otherwise be interpreted as regular expressions.

Alternatively, you could create an extra column, say D. (I'm assuming that your rightmost numbers are in column C, not part of the contents of column B, in fact.) Enter =LEFT(B1) into D1 and fill down; =SUMIF(D1:D4;"a";A1:A4) or =SUMIF(D1:D4;D5;A1:A4) or =SUMIF(D1:D4;LEFT(B5);A1:A4) will then achieve what you need. If preferred, column D can be hidden, excluded from a print range, or placed out of the way or even on another sheet.

I trust this helps.

Brian Barker

#3

In this particular case I want to sumifs() with the * wildcard for
compatibility with Excel.
Regexp are not supported by Excel.

#4

You need to wait a couple of months for the next release 5.2, it implements
the option to use regular expressions or wildcard.

https://wiki.documentfoundation.org/ReleaseNotes/5.2#Support_wildcards_to_be_compatible_with_XLS.2FXLSX_and_with_ODF_1.2

Miguel Ángel.

#5

Thanks for that link Miguel. Looking forward to that feature.

#6

Hi Bruce,

we have already a beta of the upcoming 5.2.

http://dev-builds.libreoffice.org/pre-releases/

You can test it. It will install parallel to your normal version as LibreOfficeDev.

Kind regards
Regina

Bruce Hohl schrieb: