Return-Path: 
 <ooo-issues-return-12996-apmail-incubator-ooo-issues-archive=incubator.apache.org@incubator.apache.org>
X-Original-To: apmail-incubator-ooo-issues-archive@minotaur.apache.org
Delivered-To: apmail-incubator-ooo-issues-archive@minotaur.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 2741CD7F7
	for <apmail-incubator-ooo-issues-archive@minotaur.apache.org>;
 Wed,  4 Jul 2012 10:28:46 +0000 (UTC)
Received: (qmail 56059 invoked by uid 500); 4 Jul 2012 10:28:46 -0000
Delivered-To: apmail-incubator-ooo-issues-archive@incubator.apache.org
Received: (qmail 56001 invoked by uid 500); 4 Jul 2012 10:28:44 -0000
Mailing-List: contact ooo-issues-help@incubator.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:ooo-issues-help@incubator.apache.org>
List-Unsubscribe: <mailto:ooo-issues-unsubscribe@incubator.apache.org>
List-Post: <mailto:ooo-issues@incubator.apache.org>
List-Id: <ooo-issues.incubator.apache.org>
Reply-To: ooo-issues@incubator.apache.org
Delivered-To: mailing list ooo-issues@incubator.apache.org
Received: (qmail 55940 invoked by uid 99); 4 Jul 2012 10:28:43 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 04 Jul 2012 10:28:43 +0000
X-ASF-Spam-Status: No, hits=-2000.0 required=5.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.123] (HELO sif.zones.apache.org) (140.211.11.123)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 04 Jul 2012 10:28:31 +0000
Received: by sif.zones.apache.org (Postfix, from userid 80)
	id 10F37470A; Wed,  4 Jul 2012 10:28:11 +0000 (UTC)
From: bugzilla@apache.org
To: ooo-issues@incubator.apache.org
Subject: [Bug 65221] SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM, ... with empty
 cells and "" and ="" empty string criteria
Date: Wed, 04 Jul 2012 10:28:10 +0000
X-Bugzilla-Reason: CC
X-Bugzilla-Type: changed
X-Bugzilla-Watch-Reason: None
X-Bugzilla-Product: spreadsheet
X-Bugzilla-Component: code
X-Bugzilla-Keywords: 
X-Bugzilla-Severity: trivial
X-Bugzilla-Who: zteve.powell@gmail.com
X-Bugzilla-Status: ACCEPTED
X-Bugzilla-Priority: P3
X-Bugzilla-Assigned-To: ooo@erack.de
X-Bugzilla-Target-Milestone: AOO Later
X-Bugzilla-Changed-Fields: cc
Message-ID: <bug-65221-248469-qqny1yP8xi@https.issues.apache.org/ooo/>
In-Reply-To: <bug-65221-248469@https.issues.apache.org/ooo/>
References: <bug-65221-248469@https.issues.apache.org/ooo/>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit
X-Bugzilla-URL: https://issues.apache.org/ooo/
Auto-Submitted: auto-generated
MIME-Version: 1.0
X-Virus-Checked: Checked by ClamAV on apache.org

https://issues.apache.org/ooo/show_bug.cgi?id=65221

ztevepowell <zteve.powell@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |zteve.powell@gmail.com

--- Comment #20 from ztevepowell <zteve.powell@gmail.com> ---
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.