# Bug in my sumif() regex [closed]

I have a calc spreadsheet with an attempt at a sumif expression that I haven't been able to get right. The expression I have coded is: =SUMIF(B6:B93,"=*",(D6:D93)-(C6:C93)) and here is a sample of four rows of data in columns B-D:

B       C              D
*   (blank) $200.00$170   $100.00 *$150   $600.00 * (blank)$300.00

I expect this to return \$950, but I end up with  #VALUE!


Please post if you can spot my error. Thanks!

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2019-10-26 16:36:25.543551

Where's the "regex"?

BTW:
The comma used as as the parameter delimiter conflicts with the decimal delimiter for the majority of locales. Better use the semicolon. (See https://ask.libreoffice.org/en/questi....)

( 2019-10-22 21:18:56 +0200 )edit

Thanks, Lupp. I originally wrote the expression with semicolons, and then LO changes the ;'s to ,'s...

( 2019-10-22 23:00:49 +0200 )edit

This is default in locales with the comma being the decimal separator for some time now. You can change the setting in >Tools>Options>LibreOffice Calc>Formula>Separators>Function.

( 2019-10-22 23:54:47 +0200 )edit

Sort by » oldest newest most voted

Hello,

Force array functionality for SUMIF using =SUMPRODUCT(SUMIF(B6:B93,"=*",(D6:D93)-(C6:C93))) or use array function as {=SUMIF(B6:B93,"=*",(D6:D93)-(C6:C93))} (enter without curly brackets and use CTRL+SHIFT+ENTER instead of just ENTER (and don't put the formula into an cell within any of the ranges)

Hope that helps.

more

But if I'm not wrong the "=*" doesn't find anything, if it is to find any not empty then "=.*" or ¨.*" or "=.+" or ".+", =SUMPRODUCT(ISBLANK(B6:B93)<1;D6:D93-C6:C93)

( 2019-10-22 22:21:50 +0200 )edit

@m.a.riosv - not sure; but generally I'm pasting formulas just copied from my test. And in the case above, it is about to find "*" literally and not being a placeholder (as @Lupp commented)

( 2019-10-22 22:27:24 +0200 )edit

Ok I see now, forgive.

( 2019-10-22 22:33:39 +0200 )edit

There's nothing to forgive - comments are always welcome, if their intention is to clarify facts (which your's definitely is about).

( 2019-10-22 22:36:25 +0200 )edit

I didn't the sample exactly as the questioner, I am also used to copy the formulas from a sample sheet.

( 2019-10-22 22:39:14 +0200 )edit

"Hope that helps."

Wonderful. Thanks!!!

( 2019-10-22 23:07:59 +0200 )edit

"Hope that helps."

It does, Thanks!!! But now I'm baffled by what the braces that arise from CTRL-SHIFT-ENTER are doing. Can you point me to the part of help that discusses C-S-E and other things like that please?

( 2019-10-22 23:15:47 +0200 )edit
1

As stated in my answer the curly brackets make the SUMIF function aware of using arrays (i.e. it makes it calculating row by row what you intend to calculate: D6:D93-C6:C93 should mean: D6-C6, D7-C7, ....D93-C93 (with respect to the condition). If you would just use ENTER you'd get a 504 - Error in parameter list. And the alternative using SUMPRODUCT does the same thing (forcing to apply array functionality to the following function)

You may also have a look into the following demonstration file, breaking down the formula to help understanding how it works.
C:\fakepath\SUMIF-Array.ods