Apache OpenOffice (AOO) Bugzilla – Issue 113183
COUNTIF in a linked document when the condition is a digital string
Last modified: 2017-05-20 10:31:03 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.
Created attachment 70643 [details] zipped documents
confirm with OOo DEV300m84. It works fine with OOo 2.4.3.
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.
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.
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.
Grabbing issue. Treat external reference (thus matrix) the same as normal reference.
Code change reviewed.
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
Reassigning to QA for verification.
Seen ok in cws calc66 -> verified