Bug 133260

Created attachment 161177 [details]
Edited sample file

For me:
=SUMIFS(D8:D14;C8:C14;{"li"|"liu"}) => 9812

{=SUMIFS(D8:D14;C8:C14;{"li"|"liu"})}  => 9812   5689

=SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) => 9812

{=SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"}))} => 15501

I have not excel to test but I think you need to enter it as array [Ctrl+Shift+Enter]

Hi Miguel,

Thanks for helping.

(In reply to m.a.riosv from comment #1)
> For me:
> =SUMIFS(D8:D14;C8:C14;{"li"|"liu"}) => 9812
> 
> {=SUMIFS(D8:D14;C8:C14;{"li"|"liu"})}  => 9812   5689
> 
> =SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) => 9812
> 
> {=SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"}))} => 15501
> 
> I have not excel to test but I think you need to enter it as array
> [Ctrl+Shift+Enter]
I can indeed get the desired 15501 result using Shift+Ctrl+Enter on 6.4.4, it didn't work on 6.2.8 though.

Also I disagree "using Shift+Ctrl+Enter to input as if handling an array" is the proper solution for this bug, it's at most a workaround, because:

1. The major concern of the original reporter is compatibility with Excel (and Kingsoft's WPS), since in Excel simply =SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) gives 15501;

2. Even from a logical point of view, the Calc behavior is questionable.  Using Shift+Ctrl+Enter for =SUMIFS(D8:D14;C8:C14;{"li"|"liu"}) is justified because it returns an array.  However adding SUM() on top of it, =SUM(SUMIFS(D8:D14;C8:C14;{"li"|"liu"})) should be summing over an array and returning a scalar answer, and therefore shouldn't need Shift+Ctrl+Enter.

(In reply to zh_CN User Community Liaison from comment #2)
> I can indeed get the desired 15501 result using Shift+Ctrl+Enter on 6.4.4,
> it didn't work on 6.2.8 though.
Actually, it's not the difference between 6.2.8 and 6.4.4 at all, but rather:

1. Make sure recalculation is done on load (Tools > Options > LibreOffice Calc > Formula > Recalculaion on File Load > Excel 2007 or newer, set to "Always recalculate";

2. Open the original sample file attachment 161113 [details];

3. Click in Cell D17, it shows the wrong result 9812;

4. Press Shift+Ctrl+Enter to try to change it to an array input;

5. [Here is what's wrong] Nothing changes, D17 is still 9812, checking D17's formula, no curly brackets so not an array.  Pressing Ctrl+Shift+Enter in formula bar without modifying the formula doesn't work either;

7. However, if one modifies the formula then press Shift+Ctrl+Enter, now it works, D17 shows correct result and the fomular has outmost curly brackets.

But this is a minor issue and probably will be resolved along with the main issue reported here.

Can we please have an original Excel sample file attached, not a WPS generated one, to see what Excel actually stores for this case? Thanks.

And yes, one has to modify a formula expression before a change is detected, otherwise the formula cell is not re-entered, appending and removing a blank is already enough. This is independent of whether a function would force array mode or not.

Btw, can someone please check if Excel forces array mode whenever an inline array is present as argument where the parameter expects a scalar value (like SUMIFS() 3rd parameter)? For example
=SUM(ABS({-2,-4}))
does that yield 2 or 6?

(In reply to Eike Rathke from comment #4)
> Can we please have an original Excel sample file attached, not a WPS
> generated one, to see what Excel actually stores for this case? Thanks.
Sure, asked the original reporter for one on the Chinese forum.

[...]
> Btw, can someone please check if Excel forces array mode whenever an inline
> array is present as argument where the parameter expects a scalar value
> (like SUMIFS() 3rd parameter)? For example
> =SUM(ABS({-2,-4}))
> does that yield 2 or 6?
This is easier to test.  According to two users with "Microsoft Office Home and Student 2019" and "Microsoft 365" respectively, Excel returns 6 for that formula.

So does this mean the scope of the issue is much larger than just SUMIFS()?

Yes. It looks like an inline array (unlike a cell range reference) as argument where a scalar value is expected would force array mode of the entire formula expression. Question remains what happens if it is not an inline array but an array/matrix returned from a function that itself is not in a forced array context. The results of these examples could be interesting:

=SUM(ABS(MUNIT(2)))

=SUM(ABS(MUNIT(2)*-1))

If both behave the same as with the inline array the result should be 2 for both, otherwise 1 (for one or both expressions).

Created attachment 161512 [details]
Same sample file as 161113, saved by MS Excel

Sample file saved by Excel attached.

Also,
(In reply to Eike Rathke from comment #6)
> Question remains what happens if it is not an
> inline array but an array/matrix returned from a function that itself is not
> in a forced array context. The results of these examples could be
> interesting:
> 
> =SUM(ABS(MUNIT(2)))
> 
> =SUM(ABS(MUNIT(2)*-1))
> 
> If both behave the same as with the inline array the result should be 2 for
> both, otherwise 1 (for one or both expressions).
Excel returns 2 for both formula.

Taking.

Can someone please check this in Excel:

A1: -4
A2: -8
A4: ={-1;-2}+ABS(A1:A2)
A5: =ABS(A1:A2)+{-1;-2}
A6: =SUM({-1;-2}+ABS(A1:A2))
A7: =SUM(ABS(A1:A2)+{-1;-2})

What are the results in A4:A7?

(In reply to Eike Rathke from comment #9)
> Can someone please check this in Excel:
> 
> A1: -4
> A2: -8
> A4: ={-1;-2}+ABS(A1:A2)
> A5: =ABS(A1:A2)+{-1;-2}
> A6: =SUM({-1;-2}+ABS(A1:A2))
> A7: =SUM(ABS(A1:A2)+{-1;-2})
> 
> What are the results in A4:A7?
According to multiple testers, all four cells give "#VALUE!" errors in Excel.

Brilliant..
Thanks!

Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/3a33828b8de7554e497051738c722b1764960a86

Resolves: tdf#133260 Propagate ForceArrayReturn from inline arrays

It will be available in 7.1.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 Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/5d9e33068e756b4e74aa2a5e8d9ed16dabe27f29

Derive FormulaExternalToken from FormulaByteToken, tdf#133260 follow-up

It will be available in 7.1.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.

Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/dd78ef9ff26fa35a03ac655b0d96738170e8e08b

tdf#133260: sc_ucalc: Add unittest

It will be available in 7.1.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.

Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a75799f3e904bd46cffd0bac78e3c0885ac5c447

tdf#133260: move unittest to function test

It will be available in 7.2.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.

Using steps in Comment 3, verified as fixed in:

Version: 7.2.0.2 / LibreOffice Community
Build ID: 614be4f5c67816389257027dc5e56c801a547089
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded