Download it now!
Bug 133260 - FILEOPEN XLSX: Calc doesn't handle SUMIFS() with array as the third parameter correctly
Summary: FILEOPEN XLSX: Calc doesn't handle SUMIFS() with array as the third parameter...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.6.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL: https://bbs.libreofficechina.org/thre...
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2020-05-22 05:30 UTC by zh_CN User Community Liaison
Modified: 2020-06-02 11:12 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file using SUM(SUMIFS()) with array as the third parameter of SUMIFS (8.79 KB, application/zip)
2020-05-22 05:30 UTC, zh_CN User Community Liaison
Details
Edited sample file (5.61 KB, application/zip)
2020-05-23 01:44 UTC, m.a.riosv
Details
Same sample file as 161113, saved by MS Excel (9.18 KB, application/zip)
2020-06-02 09:39 UTC, zh_CN User Community Liaison
Details

Note You need to log in before you can comment on or make changes to this bug.
Description zh_CN User Community Liaison 2020-05-22 05:30:18 UTC
Created attachment 161113 [details]
Sample file using SUM(SUMIFS()) with array as the third parameter of SUMIFS

This bug is forwarded and translated from a report in a Chinese user forum, original report (in Chinese) at: https://bbs.libreofficechina.org/thread-2459-1-1.html

The issue is that in MS Excel the function SUMIFS() allows its third parameter "criteria1" to be an array, and the return value is also an array corresponding the SUMIFS() result with the criteria being each member of the parameter array.

The attached example shows one usage of this by using SUM() on the SUMIFS() result array and calculate the sum of different people's salaries.  This works in Excel but not in Calc.  The expected value for cell D17 is 15501 (sum of D10 and D13), while in Calc (after "Recalculate Hard" if necessary) D17 is 9812 (just D10).

On the other hand, I can't find official documentation saying SUMIFS() supports array as the third parameter, and I don't know what the specification says, so I'm filing as an enhancement request.

The original report is for version 6.3.6 on Windows 10 and 6.4.3 on Ubuntu 20.04, I've reproduced with 6.4.4:
Version: 6.4.4.2 (x64)
Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff
CPU threads: 2; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; 
Locale: zh-CN (zh_CN); UI-Language: en-US
Calc: threaded
Comment 1 m.a.riosv 2020-05-23 01:44:22 UTC
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]
Comment 2 zh_CN User Community Liaison 2020-05-23 14:41:07 UTC
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.
Comment 3 zh_CN User Community Liaison 2020-05-23 15:08:49 UTC
(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.
Comment 4 Eike Rathke 2020-05-25 14:51:24 UTC
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?
Comment 5 zh_CN User Community Liaison 2020-05-26 10:13:52 UTC
(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()?
Comment 6 Eike Rathke 2020-05-26 18:16:26 UTC
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).
Comment 7 zh_CN User Community Liaison 2020-06-02 09:39:00 UTC
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.