Issue 113183 - COUNTIF in a linked document when the condition is a digital string
Summary: COUNTIF in a linked document when the condition is a digital string
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 3.2.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: 3.4.0
Assignee: kla
QA Contact: kla
Keywords: regression
Depends on:
Reported: 2010-07-16 11:17 UTC by fyva
Modified: 2017-05-20 10:31 UTC (History)
6 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

zipped documents (13.03 KB, application/x-compressed)
2010-07-16 11:21 UTC, fyva
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description fyva 2010-07-16 11:17:39 UTC
If a cell contains only digits and is formatted as text (ie 123) or has an "'"
before the digits (ie '123) and the COUNTIF contains a link to another document
(ie =COUNTIF('file:///F:/Shared/original.ods'#$Sheet1.A9:B10;A2)) then the
formula doesn't work. COUNTIF does not count 123 in the linked document to be
equal to 123 in the current document. COUNTIF() don't work in this case (but
IF() still works). See attachment below.
Comment 1 fyva 2010-07-16 11:21:35 UTC
Created attachment 70643 [details]
zipped documents
Comment 2 helenrussian 2010-07-17 08:02:26 UTC
confirm with OOo DEV300m84.

It works fine with OOo 2.4.3.
Comment 3 rollepmt 2011-01-23 19:15:12 UTC
I was also able to replicate the problem by using the supplied attachment in 
both of the following test environments:

Open Office 3.2.1 Build: 9502
OS: Windows 7 Ultimate (32-bit)

Open Office 3.2.1 Build: 9502
OS: Mac OS X Version 10.6.5

Several other tests were done, which included:

1. Including the "'" before each cell that contains a digit in one or both 

2. Creating new files with different numbers.

3. Creating a new case within Microsoft Excel 2003 (that works) and trying it 
within Calc.

In every case, COUNTIF() failed but IF() worked within Calc.

This issue is important as it could possibly hamper user's ability to accurately 
produce documents and reports when using this version of OO, especially if 
porting documents over between applications.
Comment 4 rollepmt 2011-01-24 17:34:56 UTC
A few steps for replicating (without the files above):

1. Open two new spreadsheets in OO Calc.

2. In one spreadsheet, fill the four cells of column A with the following values:
   a. a text-formatted digit (e.g. 123).
   b. a text-formatted digit preceeded by " ' " (e.g. '123).
   c. a non-formatted integer < 10 (e.g. 1).
   d. some text (e.g. "text").

3. In the other spreadsheet, fill the same cells with the same values as in step  2.

4. In the first spreadsheet, fill the first cell of column B with the COUNTIF()
formula as follows:

        =COUNTIF('filepath of other spreadsheet'#Sheet1.A1:B4;A1)

5. Still in the first spreadsheet, fill the first cell of column D with the
following IF() formula:

        =IF('filepath of other spreadsheet'#Sheet1.A1=A1)

6. For both the first cell of column B and the first cell of column D, highlight
the cell. Click the dot at the bottom left of the border that highlights the
cell and drag it down. It should cover the first four cells. When the mouse
button is released, Calc will recalculate the formula for each corresponding
cell and display the result there.

7. A binary value now appears in the first four cells of column B, and a Boolean
value in the cells of D. 

If the values in both spreadsheets are equal (which they are, and should be),
IF() should produce TRUE in each cell, which it does.

If the values are equal (which they are), COUNTIF() should produce a 1 in each
of its respective cells. Unfortunately, for values a and b, which are
text-formatted digits, it registers a value of 0, which is incorrect.

The user could not determine a work around for this bug within the version of OO.

The tests I mentioned above were reproduced from scratch, and tests all confirm
that COUNTIF() works incorrectly.

It is also interesting that this bug is also apparent with digits formatted as
decimal numbers, as well as currency-formatted digits.

Comment 5 rollepmt 2011-01-24 17:40:22 UTC
With apologies, there are two typos above.

For the two formulae COUNTIF() and IF(), directly after the # symbol and
directly before the S near the end, there should be an $ symbol. This ensures
the cells are populated correctly.
Comment 6 ooo 2011-02-15 12:40:14 UTC
Grabbing issue.
Treat external reference (thus matrix) the same as normal reference.
Comment 7 niklas.nebel 2011-02-28 11:32:01 UTC
Code change reviewed.
Comment 8 ooo 2011-03-03 13:10:05 UTC
In cws calc66:

changeset 55737a716866
M sc/source/core/tool/interpr1.cxx

You can observe the progress and possible integration date of CWS calc66 at
Comment 9 ooo 2011-03-15 12:56:48 UTC
Reassigning to QA for verification.
Comment 10 kla 2011-03-21 10:10:53 UTC
Seen ok in cws calc66 -> verified