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

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