Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 09 Feb 2019 11:13:20 +0100Why is this SUMIF not working?https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/A formula using SUMIF doesn't seem to be giving the expected answer. A screenshot is attached. What it's meant to do is to sum items in column I when there's no date in column M. It works in rows 78 & 80, but not in row 79.
It would be good if one could put a formula like NOT(ISBLANK()) into the criterion, but this doesn't seem to be possible.
![image description](/upfiles/15495449794378234.png)Thu, 07 Feb 2019 14:09:57 +0100https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/Comment by SM_Riga for <p>A formula using SUMIF doesn't seem to be giving the expected answer. A screenshot is attached. What it's meant to do is to sum items in column I when there's no date in column M. It works in rows 78 & 80, but not in row 79.</p>
<p>It would be good if one could put a formula like NOT(ISBLANK()) into the criterion, but this doesn't seem to be possible.</p>
<p><img alt="image description" src="/upfiles/15495449794378234.png"></p>
https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182228#post-id-182228Hello @ptoye Does condition ">0" return correct result?Thu, 07 Feb 2019 15:11:33 +0100https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182228#post-id-182228Answer by erAck for <p>A formula using SUMIF doesn't seem to be giving the expected answer. A screenshot is attached. What it's meant to do is to sum items in column I when there's no date in column M. It works in rows 78 & 80, but not in row 79.</p>
<p>It would be good if one could put a formula like NOT(ISBLANK()) into the criterion, but this doesn't seem to be possible.</p>
<p><img alt="image description" src="/upfiles/15495449794378234.png"></p>
https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?answer=182232#post-id-182232A Criterion context of`<>0` behaves as in *For <>, if the value is not empty it matches any cell content except the value, including empty cells.* (as defined in ODF OpenFormula (ODFF) 4.11.8 Criterion).
Instead, use `=SUMPRODUCT($M$1:$M79<>0;$I$1:$I79)`
Yes, the `<>` operator (not to be confused with a criterion) behaves different.
Thu, 07 Feb 2019 15:33:59 +0100https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?answer=182232#post-id-182232Comment by SM_Riga for <p>A Criterion context of<code><>0</code> behaves as in <em>For <>, if the value is not empty it matches any cell content except the value, including empty cells.</em> (as defined in ODF OpenFormula (ODFF) 4.11.8 Criterion).</p>
<p>Instead, use <code>=SUMPRODUCT($M$1:$M79<>0;$I$1:$I79)</code></p>
<p>Yes, the <code><></code> operator (not to be confused with a criterion) behaves different.</p>
https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182338#post-id-182338[SUMPRODUCT function](https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_SUMPRODUCT_function) returns the sum of the products of corresponding array elements - ir calculates product of first element of each array, then product of second element and so on till last element and then sums products together.
> Is this an array which is '1' if the
> value isn't 0 and '0' if it is?
Yes, this part tests each cell in `$M$1:$M79` range if it's value is 0 or not and returns an array of '1' and '0' (`TRUE` or `FALSE`). Then each element of this array is multiplied by corresponding element (cell value) in range `$I$1:$I79`. So you get product 0 where `$M$1:$M79<>0`test was FALSE (0), and cell value from`$I$1:$I79`range where test was TRUE (1). Then `SUMPRODUCT` sums these products together and returns result.Fri, 08 Feb 2019 12:37:34 +0100https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182338#post-id-182338Comment by ptoye for <p>A Criterion context of<code><>0</code> behaves as in <em>For <>, if the value is not empty it matches any cell content except the value, including empty cells.</em> (as defined in ODF OpenFormula (ODFF) 4.11.8 Criterion).</p>
<p>Instead, use <code>=SUMPRODUCT($M$1:$M79<>0;$I$1:$I79)</code></p>
<p>Yes, the <code><></code> operator (not to be confused with a criterion) behaves different.</p>
https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182430#post-id-182430Thanks @SM_Riga I hadn't realised that you could use a Boolean function as an arithmetic value. A bit dangerous, as not all languages have the same values for False and True. And it doesn't seem to me mentioned anywhere explicitly in the help documentation, although it's hinted at in the "formula" page.Sat, 09 Feb 2019 11:13:20 +0100https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182430#post-id-182430Comment by SM_Riga for <p>A Criterion context of<code><>0</code> behaves as in <em>For <>, if the value is not empty it matches any cell content except the value, including empty cells.</em> (as defined in ODF OpenFormula (ODFF) 4.11.8 Criterion).</p>
<p>Instead, use <code>=SUMPRODUCT($M$1:$M79<>0;$I$1:$I79)</code></p>
<p>Yes, the <code><></code> operator (not to be confused with a criterion) behaves different.</p>
https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182238#post-id-182238Voted for `SUMPRODUCT` solution. To @ptoye Wonder why `row78` and `row80` are considered as correct in the question. `"<>0"`shoud result in SUM(I1:I79) regardless of M column contents. By the way, `<>0` was considered to return non-empty cells, while in question stated "What it's meant to do is to sum items in column I when there's no date in column M".Thu, 07 Feb 2019 16:15:06 +0100https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182238#post-id-182238Comment by ptoye for <p>A Criterion context of<code><>0</code> behaves as in <em>For <>, if the value is not empty it matches any cell content except the value, including empty cells.</em> (as defined in ODF OpenFormula (ODFF) 4.11.8 Criterion).</p>
<p>Instead, use <code>=SUMPRODUCT($M$1:$M79<>0;$I$1:$I79)</code></p>
<p>Yes, the <code><></code> operator (not to be confused with a criterion) behaves different.</p>
https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182335#post-id-182335Thanks @erAck - that seems to work fine, so I've voted it up.
But I have to say that I can't work out why. The LO help system gives me no hint that I can find as to what `$M$1:$M79<>0` even means! Even the syntax looks odd. Is this an array which is '1' if the value isn't 0 and '0' if it is?
I looked at ODF, but that's described as a document interchange format (as I understand it), not an LO formula syntax, even if the two follow each other. Their website is promotional stuff, and doesn't seem to have links to technical documentation, so I can't follow your reference to section 4.11.8 of ODFF (which doesn't even Google usefully!)Fri, 08 Feb 2019 12:12:08 +0100https://ask.libreoffice.org/en/question/182220/why-is-this-sumif-not-working/?comment=182335#post-id-182335