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.
A 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.
Voted 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”.
Thanks @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!)
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<>0test was FALSE (0), and cell value from$I$1:$I79range where test was TRUE (1). Then SUMPRODUCT sums these products together and returns result.
Thanks @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.