Ask Your Question

Bug in my sumif() regex [closed]

asked 2019-10-22 18:38:09 +0200

Al Vesper gravatar image

updated 2019-10-22 18:41:11 +0200

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 flag offensive 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"?

The comma used as as the parameter delimiter conflicts with the decimal delimiter for the majority of locales. Better use the semicolon. (See

Lupp gravatar imageLupp ( 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...

Al Vesper gravatar imageAl Vesper ( 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.

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

1 Answer

Sort by » oldest newest most voted

answered 2019-10-22 20:00:21 +0200

Opaque gravatar image

updated 2019-10-22 20:02:43 +0200


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.

edit flag offensive delete link 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)

m.a.riosv gravatar imagem.a.riosv ( 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)

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

Ok I see now, forgive.

m.a.riosv gravatar imagem.a.riosv ( 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).

Opaque gravatar imageOpaque ( 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.

m.a.riosv gravatar imagem.a.riosv ( 2019-10-22 22:39:14 +0200 )edit

"Hope that helps."

Wonderful. Thanks!!!

Al Vesper gravatar imageAl Vesper ( 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?

Al Vesper gravatar imageAl Vesper ( 2019-10-22 23:15:47 +0200 )edit

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)

For more information start here:
You may also have a look into the following demonstration file, breaking down the formula to help understanding how it works.

And if the answer helped to solve your problem, please click the check mark (✔) next to the answer.

Opaque gravatar imageOpaque ( 2019-10-22 23:28:54 +0200 )edit

Question Tools

1 follower


Asked: 2019-10-22 18:38:09 +0200

Seen: 90 times

Last updated: Oct 22 '19