Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||COUNTIF in a linked document when the condition is a digital string|
|Status:||CLOSED FIXED||QA Contact:||kla <thomas.klarhoefer>|
|Priority:||P3||CC:||dr_faust, helenrussian, issues, kozodaevroman, nesshof, niklas.nebel|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
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 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 files. 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 http://hg.services.openoffice.org/cws/calc66/changeset/55737a716866 M sc/source/core/tool/interpr1.cxx You can observe the progress and possible integration date of CWS calc66 at http://tools.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fcalc66
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