Hi all, I’m using for a long time a spreadsheet in LibreOffice Calc with formulas of this type:
{=SOMMA( (H27:H3001=1)*(MESE(A27:A3001)=N20)*J27:J3001 )}
to perform a conditional sum over a range of cells, but from version 5 I get the ‘#value!’ error, so I can’t use newer versions of the product.
Is there a way to make compatible the formula with newer versions?
(The formula works on Excel too).
Thanks
This looks a bit as if the array-evaluation is broken for your formula. (It does not occur for me.)
I would suggest you first try =SUMPRODUCT(H27:H3001=1;MONTH(A27:A3001)=N20;J27:J3001) which should implicitly force array-evaluation.
Please report again.
(There is an option under > ‘Tools’ > ‘Options’ > ‘LibreOffice Calc’ > ‘Formula’ to use English function names independent of the chosen UI language. This setting obviously has advantages if help from an international forum is needed.)
Thanks for the answer, Lupp.
The problem stills even with your suggestion.
Making some test, it seems to me a problem with the function month(), since in the interval specified there are many empty dates in the “A” column, and the function month() applied to an empty cell returns the #value! error.
In the old release 4.2.8.2 and earlier this problem does not occur.
Maybe the simproduct() formula evaluates now ALL factors of the product, even if the first of them evaluates to 0 (H27:H3001=1 returns 0 or False in that case).
Usually for code optimization, in many programming languages. in a product operation the engine evaluates the factors in order from left to right, and when the first of them is 0 the evaluation stops, becouse we are shure that the result must be 0.
I suppose (but I can be wrong) that this does not happen in the newer releases of LO.
What about? Is there a workaround?
(Regarding the original question and the self-answer by the questioner as well:)
I cannot confirm anything. My example made with LibO 5.2.2 is working as expected under my standard locale ‘German (Germany)’ and under ‘Italian (Italy)’ as well. This in three versions: Using the OQ’s original formula design, using SUMPRODUCT in the way I suggested, and using the SUMIFS function which is supposed to solve the purpose under discussion.
There is a faint suspicion the newly observed aberrative behaviour of Calc on the OQ’s system might be due to user profilae corruption.
Concerning the supposed behaviour with respect to short-circuiting evaluation I just would state that spreadsheets are notoriously inefficient anyhow. Short-circuiting is explicitly specified only for the IF function insofar as only one of the alternative expressions shall be evaluated. A respective behaviour is specified mandatorily for CHOOSE.
@EmannoF: Please tell me if (and in what way) the example does either not work as expected on your system or does not meet your needs due to some misunderstanding on my behalf.
the example is perfect, it works with all three methods
In my case, instead, the sumif() only function solves the purpose, the other two mrthods does not.
I think the faint suspicion is concrete fact…
Thanks so much for the help