The following resolves to 0 rather, than resolving to 60, which it would resolve to if a cell reference range to cells of the same values was given. =SUMIF({-10,10,20,30},">0") Could support for passing arrays to SUMIF and SUBTOTAL be added?
Several points. In addition to this tdf#158789, you also filed tdf#158788, apparently trying to solve some issue with how to arrive to some result. Please understand that most of us are volunteers here. This site is for reporting bugs and enhancement requests, not a support forum. You could go to <https://ask.libreoffice.org> for that, for instance. Now, as for the result you seem to be trying to obtain, I am about to post one possible alternative. This is not a complete solution, and most probably not even an efficient formula; it is just a way to show you that instead of asking for changing a function here you could just go to some forum and ask for help in finding the result you want. So, here is one way (of several) to obtain an array formula with what you need (as far as you presented it in comment 0): =SUM({-10;10;20;30}*({-10;10;20;30}>0)) to be introduced not with [Enter] but with [Ctrl]+[Shift]+[Enter] (CSE). For further help with your formula, or better alternatives (they surely are available out there), I'm going to respectfully and politely ask you to please use a more adequate site, leaving this for actual bug reports and enhancements requests. IMO, this is neither. Others might not agree.
Thank you for being kind enough to take the time to read between the lines and try to solve the issue I'm encountering. I appreciate that. But I did intend for this to be a legitimate, standalone enhancements requests and I didn't request support. I gave an example in comment 0 to be concrete/clear, and for the convenience of anyone who might want to quickly test my claim. From an average user's point of view it seems logical to me that SUMIF should also accept an array. IMHO it would also make for a more readable and discoverable solution for non-expert users. Could my request (as stated in the bug summary) be considered on its own merits? Thank you again for your polite and helpful reply.
Hallo for example: =SUMIF(A1:A4;">0")
Hello. Yes, it works on cell ranges. But if you do any operation on the cell range before passing it to SUMIF it won't work because the range is converted to an array. For example, that can be seen by tweaking your example to: =SUMIF(A1:A4*1;">0")
SUMIF doesnt (and wasnt!) work this way! ⇒ resolved ⇒ notabug
Sure, I understand it doesn't currently work that way, as shown in comment 0 when I asked "Could support...be added?" But Google Sheets and Zoho Sheets both support this FWIW. And it seems inconsistent to me that arrays work in SUM but not SUMIF: =SUM({-10,10,20,30}) <- works =SUMIF({-10,10,20,30},">0") <- doesn't work That said, it's the perogative of those of you who actually write the LibreOffice code to decide if you want to accept this enhancement request or not. Thanks for your time. And thanks for your work on LibreOffice.
As detailed in https://ask.libreoffice.org/t/is-there-an-alternative-to-sumif-for-arrays/99693/2 Both functions work using all the parameters. =SUMIF({1;20;3};">1";{1;20;3}) or =SUMIFS({1;20;3};{1;20;3};">1")
(In reply to Werner Tietz from comment #5) The "doesnt (and wasnt!) work this way" is *never* a reason to close an enhancement request. Enhancements are exactly to make something to happen, that wasn't here before. OP didn't set the Enhancement properly; but they made an effort to express this ("Could support for passing arrays to SUMIF and SUBTOTAL be added?" in comment 0, also in comment 2). Triaging should not behave in a robotic way. I support the request - based on comment 7, which definitely shows that the functions do support inline arrays *in some form*, so not supporting them without the optional third argument is inconsistent; and the specific detail of the *current* behavior is a real bug: *if* the functions follow ODF spec literally (as in "Constraints: Does not accept constant values as the reference parameter" on ODF 1.3 6.13.9 COUNTIF, 6.16.62 SUMIF, etc.), then it must emit an *error* when user passes inline array with constant values in that argument, not a 0 with absent third argument, nor a correct result with the third argument present. So the current 0 is a bug from any point of view. I revert it back to UNCONFIRMED. Please update the status based on some better arguments.
Note also, that there is an interoperability aspect here. Gnumeric, Google Sheets, and some other spreadsheet programs (though not Excel, notably) do support the requested syntax.
I agree with Mike that the inconsistency is problematic, and would support more interoperability with other tools. However, looking at the ODF specification[1], I understand using an array on the first argument is not allowed: "Constraints: Does not accept constant values as the range parameter." ...which contradicts the working example in comment 7. Regina, what is your opinion? Any potential future evolution in ODF here? Or maybe I am reading the spec wrong? Regarding SUBTOTAL: arrays already work, e.g.: =SUBTOTAL(1,{1,2,3,4,5}) Jonathan, can you confirm? [1]: https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1018584_715980110
(In reply to Mike Kaganski from comment #8) > and the specific detail > of the *current* behavior is a real bug: *if* the functions follow ODF spec > literally (as in "Constraints: Does not accept constant values as the > reference parameter" on ODF 1.3 6.13.9 COUNTIF, 6.16.62 SUMIF, etc.), then > it must emit an *error* when user passes inline array with constant values > in that argument, not a 0 with absent third argument, nor a correct result > with the third argument present. So the current 0 is a bug from any point of > view. Indeed.. the implementation is wrong. In the case of a matrix to be queried and missing 3rd parameter it iterates over an "imaginary" (likely empty set, didn't check) cell range. I don't know if that was trying to mimic some hidden Excel feature nor recall why it was ever implemented that way (probably because Excel does not support the array case), it's there since 2009 already, and wasn't handled at all earlier. See the else{} block at https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx?r=2e1f9da8#5602 That should be executed only with 3 parameters and a cell range given as 3rd. To me it looks like a 3rd branch is missing for the case that no 3rd argument (neither matrix nor cell range) was given, that sums elements of pQueryMatrix for pResultMatrix conditions fulfilled. That ScInterpreter::IterateParametersIf() is used for both SUMIF() and AVERAGEIF(). COUNTIF() has a different implementation and works as expected. ODFF constraint for these functions is likely there because of Excel. I suggest to not follow it but rather add a remark in the standard, as more implementations do support that feature than don't.
(Sorry Mike, excuse the repeated info, I somehow completely missed how you already mentioned the standard in comment 8!)
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/a523e9bf2d54fc84583c9da05af592297b055e40 Resolves: tdf#158789 operate on query array if no extra array or range given It will be available in 24.8.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.
Pending review https://gerrit.libreoffice.org/c/core/+/168315 for 24-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-24-2": https://git.libreoffice.org/core/commit/5af051e9e6b0f86fe471c4f040506ee9fdb91e92 Resolves: tdf#158789 operate on query array if no extra array or range given It will be available in 24.2.5. 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/d804d6aff49054bad1719ec3c2d136b545bbc7e7 tdf#158789: sc_mathematical_functions: Add unittest It will be available in 24.8.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.
In release notes: https://wiki.documentfoundation.org/index.php?title=ReleaseNotes%2F24.2&type=revision&diff=752732&oldid=751638