Bug 158789 - Allow SUMIF and SUBTOTAL to apply to arrays
Summary: Allow SUMIF and SUBTOTAL to apply to arrays
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL: https://ask.libreoffice.org/t/is-ther...
Whiteboard: target:24.8.0 target:24.2.5 inRelease...
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2023-12-20 02:58 UTC by Jonathan Watt
Modified: 2024-06-04 13:30 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Jonathan Watt 2023-12-20 02:58:12 UTC
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?
Comment 1 ady 2023-12-20 05:18:59 UTC
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.
Comment 2 Jonathan Watt 2023-12-20 12:12:49 UTC
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.
Comment 3 Werner Tietz 2023-12-20 12:30:38 UTC
Hallo

for example:

=SUMIF(A1:A4;">0")
Comment 4 Jonathan Watt 2023-12-20 15:29:35 UTC
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")
Comment 5 Werner Tietz 2023-12-20 19:05:12 UTC
SUMIF doesnt (and wasnt!) work this way! 
⇒ resolved ⇒ notabug
Comment 6 Jonathan Watt 2023-12-20 22:46:14 UTC
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.
Comment 7 m_a_riosv 2023-12-21 02:12:06 UTC
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")
Comment 8 Mike Kaganski 2023-12-21 09:37:36 UTC
(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.
Comment 9 Mike Kaganski 2023-12-21 09:48:38 UTC
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.
Comment 10 Stéphane Guillou (stragu) 2024-01-04 23:51:28 UTC
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
Comment 11 Eike Rathke 2024-01-05 16:53:10 UTC
(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.
Comment 12 Stéphane Guillou (stragu) 2024-01-05 18:16:00 UTC
(Sorry Mike, excuse the repeated info, I somehow completely missed how you already mentioned the standard in comment 8!)
Comment 13 Commit Notification 2024-06-01 20:22:35 UTC
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.
Comment 14 Eike Rathke 2024-06-01 20:23:23 UTC
Pending review https://gerrit.libreoffice.org/c/core/+/168315 for 24-2
Comment 15 Commit Notification 2024-06-03 11:25:15 UTC
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.
Comment 16 Commit Notification 2024-06-03 13:50:35 UTC
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.