In this particular case I want to sumifs() with the * wildcard for compatibility with Excel. Regexp are not supported by Excel.
On Fri, Jun 17, 2016 at 9:13 AM, Brian Barker <[email protected]> wrote: > At 07:37 17/06/2016 -0400, Bruce Hohl wrote: > >> 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. >> >> Tools > Options > LibreOffice Calc > Calculate Enable regular expressions >> in formulas is *off* (I don't want regexp for this worksheet). >> > > 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 > > > -- > To unsubscribe e-mail to: [email protected] > Problems? > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.libreoffice.org/global/users/ > All messages sent to this list will be publicly archived and cannot be > deleted > > -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
