Ask Your Question

Why is this SUMIF not working?

asked 2019-02-07 14:09:57 +0100

ptoye gravatar image

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

edit retag flag offensive close merge delete


Hello @ptoye Does condition ">0" return correct result?

SM_Riga gravatar imageSM_Riga ( 2019-02-07 15:11:33 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-02-07 15:33:59 +0100

erAck gravatar image

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.

edit flag offensive delete link more


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".

SM_Riga gravatar imageSM_Riga ( 2019-02-07 16:15:06 +0100 )edit

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!)

ptoye gravatar imageptoye ( 2019-02-08 12:12:08 +0100 )edit

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.

SM_Riga gravatar imageSM_Riga ( 2019-02-08 12:37:34 +0100 )edit

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.

ptoye gravatar imageptoye ( 2019-02-09 11:13:20 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-07 14:09:57 +0100

Seen: 35 times

Last updated: Feb 07