incubator-ooo-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
Subject [Bug 65221] SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM, ... with empty cells and "" and ="" empty string criteria
Date Wed, 04 Jul 2012 10:28:10 GMT

ztevepowell <> changed:

           What    |Removed                     |Added
                 CC|                            |

--- Comment #20 from ztevepowell <> ---
Hi. My problem was this:

=SUMIF(<refrange>, <>"y", <numberrange>)

only added cells that corresponded to non-empty reference cells that had
strings which were not "y".

This omitted cells that had empty reference cells, even though A1<>"y" would
have been true for those cells in the <refrange>.

The issue is complicated by the strange reading of 'geometry' of the reference
range.  This seems to remove empty cells from the <refrange> geometry before
considering which cells to sum, and only then are the corresponding cells in
the <numberrange> considered.

This is a disaster for consistency: if I generated a new column, for example,
propagate a simple test =IF((A1<>"y"),B1,0) and sum the result, I get a
different answer from the "corresponding" SUMIF. This is a serious consistency
issue which is nothing to do with XL or ODF, nor much to do with the way empty
cells are treated in boolean expressions elsewhere.

This is due to the special treatment of empty cells in reference ranges, which
is inconsistent with the other semantics.

You are receiving this mail because:
You are on the CC list for the bug.

View raw message