[Libreoffice-bugs] [Bug 133260] FILEOPEN XLSX: Calc doesn't handle SUMIFS() with array as the third parameter correctly

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=133260

--- Comment #2 from zh_CN User Community Liaison <plateauwolf at qq.com> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200523/2efcf969/attachment.htm>