Ambrogio De Lorenzo wrote:
...
So I expect that if I use sumif(A1:A5;"";B1:B5) should sum values in
B1:B5 only if the corresponding cells in column A is empty.
...
It is true that OOo3 has changed the way empty cells are handled, but
your situation is not a result of that (as far as I can tell). I get the
same result as you if I use OOo 2.4.2; that's the oldest version I have
to check.
Your situation is combining two areas that are not well-designed: SUMIF
and empty cells. SUMIF is a stange formula--well, the criteria part is
anyway, and the behavior of empty cells is inconsistent in all
spreadsheets. It is best to avoid depending on any particular behavior
of an empty cell, especially if you're concerned about moving a document
between different spreadsheet applications.
Using OOo Calc, I could not find *any* way to get SUMIF to recognize
empty cells.
Instead, I suggest you try one of these approaches:
* Avoid using empty cells: put some content in all cells of the criteria
range, and explicitly test for some value. You can use "(none)" or
something similar. Even ="" will work, but I don't recommend it.
* Use a different function that can do proper tests. Something like
=SUMPRODUCT(ISBLANK(A1:A3);B1:B3)
will work in Calc, or
=SUMPRODUCT(ISBLANK(A1:A3)+0;B1:B3)
will work in both Calc and Excel.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
