Bug 115162 - FILEOPEN XLSX SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS functions give Error:504, with reference to external xlsx files cells
Summary: FILEOPEN XLSX SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS functions give Err...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0 all versions
Hardware: All All
: medium normal
Assignee: Bartosz
URL:
Whiteboard: target:6.1.0 target:5.4.6 target:6.0.1
Keywords: filter:xlsx
: 94309 (view as bug list)
Depends on:
Blocks:
 
Reported: 2018-01-22 23:18 UTC by Gabor Kelemen
Modified: 2018-02-06 22:22 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example xlsx file to use as external data source (14.27 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 23:18 UTC, Gabor Kelemen
Details
Example file with function references to the external data source file (15.17 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 23:21 UTC, Gabor Kelemen
Details
The two files side by side. Referring to the data source gives error. (242.68 KB, image/png)
2018-01-22 23:22 UTC, Gabor Kelemen
Details
The example xlsx file to use as external data source - saved by LO 5.3.3 (14.78 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 23:23 UTC, Gabor Kelemen
Details
Example file with function references to the external data source file - saved by LO 5.3.3 (15.84 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 23:26 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2018-01-22 23:18:27 UTC
Created attachment 139282 [details]
Example xlsx file to use as external data source

Attached spreadsheets were created with LibreOffice 6.0.0.2 and LibreOffice 5.3.3. The main problem here is that functions with complex conditions do not work correctly if we refer to an external file, and we get an Error (504) message in the cell. If we use the ‘SUMIFS’ or ‘AVERAGEIFS’ or ‘COUNTIFS’ function in the local (source.xlsx) file, they work perfectly.

Steps to reproduce:
1. Create a new spreadsheet with LibreOffice Calc
2. Click on Tools then choose Options.
3. In the Options set the LibreOffice Calc Formula options just like as you can see at the attached Formula_syntax_setting.PNG file.
4. Fill at least three columns with some simple data. If you want, you could try these functions in the local file, just to make sure they work correctly.
5. Save the file as “Source.xlsx”. This will be the source file which contains the queried data.
6. Create a new spreadsheet with LibreOffice Calc and save as “Function-Error-504.xlsx”. (This will be the reference file)
7. In the “Function-Error-504.xlsx” file create a correct SUMIFS or AVAREGEIFS or COUNTIFS function which refers to the “Source file” data.
Here is an example code for a SUMIFS function: 
=SUMIFS('file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.C1:C5;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.B1:B5;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.B1;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.A1:A5;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.A1)

8. Click Ok in the Function Wizard.
9. Save and reload the file.

Actual results:
We get an Error (504) message in the cell for these functions. Even if we set only one condition in the “more conditional” functions, we got the same error. Other similar functions (SUMIF, AVERAGEIF or COUNTIF) work well if we use these steps.
These problems may (or may not) be the same as the VLOOKUP/HLOOKUP function bug #114820.

Expected results:
We shouldn't receive an error code in the cell, because the function is correct and works perfectly in the local files.

Verzió: 5.3.3.2
Build az.: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448
CPU szálak: 4; Rendszer verziója: Windows 6.2; Felületmegjelenítés: GL; Elrendezésmotor:új; 
Területi beállítások: hu-HU (hu_HU); Calc: group

Verzió: 6.0.0.2
Build az.: 06b618bb6f431d27fd2def25aa19c833e29b61cd
CPU szálak: 4; OS: Windows 6.1; Felületmegjelenítés: alapértelmezett; 
Területi beállítások: hu-HU (hu_HU); Calc: group
Comment 1 Gabor Kelemen 2018-01-22 23:21:55 UTC
Created attachment 139283 [details]
Example file with function references to the external data source file
Comment 2 Gabor Kelemen 2018-01-22 23:22:38 UTC
Created attachment 139284 [details]
The two files side by side. Referring to the data source gives error.
Comment 3 Gabor Kelemen 2018-01-22 23:23:55 UTC
Created attachment 139285 [details]
The example xlsx file to use as external data source - saved by LO 5.3.3
Comment 4 Gabor Kelemen 2018-01-22 23:26:15 UTC
Created attachment 139286 [details]
Example file with function references to the external data source file - saved by LO 5.3.3
Comment 5 MM 2018-01-23 00:03:57 UTC
It looks like a dup from bug 114820, only with different functions.
Comment 6 Gabor Kelemen 2018-01-23 08:16:55 UTC
(In reply to MM from comment #5)
> It looks like a dup from bug 114820, only with different functions.

Sure, but I think it's better to file a dupe than adding more and more similar problems to a single huge bug. 
Also, the feature works for some functions and not for others, this makes it hard to decide how specific this is.
Comment 7 Bartosz 2018-02-06 01:12:14 UTC
Review:
https://gerrit.libreoffice.org/#/c/49264/
Comment 8 Bartosz 2018-02-06 01:14:18 UTC
*** Bug 94309 has been marked as a duplicate of this bug. ***
Comment 9 Commit Notification 2018-02-06 10:30:18 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b6db65752c60de7268e4f65d7fc5ac110adb2fb0

tdf#115162 Fix support for external reference for IFS functions

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2018-02-06 13:47:39 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d4a41817aa86fffaa3a27265966bc8265d34dd0a&h=libreoffice-5-4

tdf#115162 Fix support for external reference for IFS functions

It will be available in 5.4.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2018-02-06 15:10:46 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d9fafddcf8eb37916c8a8e76af36d67c8db84a98&h=libreoffice-6-0

tdf#115162 Fix support for external reference for IFS functions

It will be available in 6.0.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Bartosz 2018-02-06 22:22:50 UTC
*** Bug 94309 has been marked as a duplicate of this bug. ***