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!
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 Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?)
Thanks, Lupp. I originally wrote the expression with semicolons, and then LO changes the ;'s to ,'s…
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.
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.
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)
@mariosv - 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)
Ok I see now, forgive.
There’s nothing to forgive - comments are always welcome, if their intention is to clarify facts (which your’s definitely is about).
I didn’t the sample exactly as the questioner, I am also used to copy the formulas from a sample sheet.
“Hope that helps.”
Wonderful. Thanks!!!
“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?
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: Array Functions
You may also have a look into the following demonstration file, breaking down the formula to help understanding how it works.
SUMIF-Array.ods
And if the answer helped to solve your problem, please click the check mark (
) next to the answer.