Re: [libreoffice-users] sumifs() with wildcard question

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 <b.m.barker@btinternet.com>
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: users+unsubscribe@global.libreoffice.org
> 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: users+unsubscribe@global.libreoffice.org
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