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
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