Created attachment 183373 [details] Sum through sheets With an array formula like: {=SUM((INDIRECT("Week"&COLUMN(A$1:E$1)&".A"&ROW(A$1:A$500))=$A2)*INDIRECT("Week"&COLUMN(A$1:E$1)&".B"&ROW(A$1:A$500)))} to sum along several sheets. In the attached sample file, it works for me with: Versión: 5.4.7.2 Id. de compilación: c838ef25c16710f8838b1faec480ebba495259d0 Subprocs. CPU: 4; SO: Windows 6.2; Repres. IU: GL; Configuración regional: es-AR (es_ES); Calc: CL Fails with: Versión: 6.4.7.2 (x64) Id. de compilación: 639b8ac485750d5696d7590a72ef1b496725cfb5 Subprocs. CPU: 4; SO: Windows 10.0 Build 19045; Repres. IU: GL; VCL: win; Configuración regional: es-ES (es_ES); Idioma de IU: es-ES Calc: CL
I can reproduce it back to Version: 6.0.0.0.alpha1+ Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9 CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3; Locale: es-ES (es_ES.UTF-8); Calc: group threaded but not in Version: 5.3.1.2 Build ID: e80a0e0fd1875e1696614d24c32df0f95f03deb2 CPU Threads: 8; OS Version: Linux 5.10; UI Render: default; VCL: gtk2; Layout Engine: new; Locale: de-DE (es_ES.UTF-8); Calc: group
That is since the implementation for bug 58874 to create reference lists in OFFSET() and INDIRECT() (bug 106478) in array mode. Comparison operators like = can't handle those. What results does Excel produce for the sample?
Created attachment 183384 [details] Simplified file showing the issue Result with excel is #!REF! (Spanish) Microsoft® Excel® para Microsoft 365 MSO (versión 2210 compilación 16.0.15726.20070) de 64 bits Like @eike has pointed, the issue is with the comparator. A formula like: =SUM($Week1.B$1:$Week5.B$8), do the sum. A formula like: {=SUM($Week1.B$1:$Week5.B$8=A2)}, gives an Err:504 Attached sample simplified file.
(In reply to m.a.riosv from comment #3) > Result with excel is #!REF! (Spanish) Thus I tend to close this as a wontfix. > Like @eike has pointed, the issue is with the comparator. Not really. The newly attached file is a different problem as it does not involve INDIRECT() and thus no reference list is created, though slightly related. The array formula {=SUM($Week1.B$1:$Week5.B$8=A2)} for the comparison (as for any operator/function that expects scalar values as operands) would have to create a 3D array of $Week1.B$1:$Week5.B$8 that simply is nowhere supported. {=ABS($Week1.B$1:$Week5.B$8)} equally does not work.
(In reply to Eike Rathke from comment #4) > ... > Thus I tend to close this as a wontfix. > Then please do it. Hope some day most of the functions can work 3D.
I doubt 3D matrices will be generally supported; in many contexts it doesn't make sense, the result couldn't even be displayed on a 2D sheet.. only functions that accept multiple ranges like SUM() and its family would be able to consume 3D and deliver a meaningful result. All iterating over arrays for scalar values expecting functions would have to be adapted and wherever 2D matrices are handled as well, just for the little benefit of a few constellations that could make sense.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/94c64c0c999eeaa91ebdc335186289046eacdc4f Related: tdf#151863 Set #REF! error if reference list is not convertible It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike, is this bug solved? I have seen your commit, but I don't know if everything is now fine or not.
Nothing solved, just the error code changed from #VALUE! to #REF!. But yes, let's close this as won't fix.