Bug 133506 - Subtotal function to obtain the number of particular non numeric values
Summary: Subtotal function to obtain the number of particular non numeric values
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.4.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsUXEval
Depends on:
Blocks: Function-Subtotal
  Show dependency treegraph
 
Reported: 2020-05-29 21:29 UTC by Thierry Munoz
Modified: 2021-03-16 11:24 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Exemple to understand the enhancement request (8.61 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-29 21:30 UTC, Thierry Munoz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Thierry Munoz 2020-05-29 21:29:52 UTC
Description:
With the subtotal function it's easy to obtain the number of filtered values with the function SUBTOTAL(3;cells_range).
But it's not possible to obtain the number of filtered values where there is a particular text value.

Steps to Reproduce:
1.Open the attachment file to understand the enhancement requested.
2.There is an array1 with autofilter in A1:B13
3.There is an array2 in A16:B19 to display, in yellow cells, the number of values (P, D and A) of the filtered array1 with column A values.
4. Now, to obtain the results in array2, we need to use an other column in array1 with a subtotal formula (for instance, in C2 the formula is "=IF(SUBTOTAL(3,B2),B2,"").

Actual Results:
It seems not possible to obtain directly this with SUBTOTAL formula.

Expected Results:
Enhance SUBTOTAL formula to count the number of particular values after applying a filter.
It could be "=SUBTOTAL(3;COUNTIF(range;criteria))" or "=SUBTOTAL(3;range;criteria)".


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Thierry Munoz 2020-05-29 21:30:58 UTC
Created attachment 161408 [details]
Exemple to understand the enhancement request
Comment 2 Thierry Munoz 2020-06-13 06:17:45 UTC
See the attachment file
Comment 3 Buovjaga 2021-03-11 08:28:57 UTC
Is this something that other spreadsheet applications offer?
Comment 4 Heiko Tietze 2021-03-15 14:55:40 UTC
LibreOffice is functionally identical to other tools, introducing proprietary functions is not our business. But since LibreOffice works like other tools you can find the same question there. This search result seems to be quite elaborated: https://exceljet.net/formula/count-visible-rows-only-with-criteria
Comment 5 Thierry Munoz 2021-03-15 15:20:59 UTC
Sorry but I don't use Excel and I don't care about if it provides this functionnality (I discovered it with the link given in the previous message).
I told for this enhancement because I needed in LibreOffice Calc.

So If I understand the answer (WONTFIX), a formula is a proprietary function and so, as Excel provides the average formula, LibreOffice shouldn't provide this ?
In others words, only an Excel user can have a high use of spreadsheet ? I don't think so.

Regards,

Thierry
Comment 6 Heiko Tietze 2021-03-15 16:35:17 UTC
None of the functions in the example are proprietary and you can use it with Excel, OpenOffice or LibreOffice. The proposed SUBTOTAL(<COUNTIF>,<RANGE>) would be special, however. We cannot implement functionality that wouldn't work with other programs, therefore WF. If you disagree, feel free to reopen.
Comment 7 Thierry Munoz 2021-03-15 17:16:38 UTC
An enhancement is supposed to provide an improvement, a powerful tool.
So why an other function couldn't be possible ? New function not in others programms to avoid conflicts.
An other solution, perhaps would be to add on 3rd term optional (not obligatory). Without this, normal behaviour.

But if I'm the only one to ask for this enhancement, I won't reopen this issue. 
Regards.
Comment 8 m.a.riosv 2021-03-15 18:53:53 UTC
I think what it's asked can be solved with the formulas on the sample file of the attachment in tdf@12089 which have resolved an issue about using SUBTOTAL inside SUMPRODUCT function.
Comment 9 Thierry Munoz 2021-03-15 20:04:28 UTC
(In reply to m.a.riosv from comment #8)
> I think what it's asked can be solved with the formulas on the sample file
> of the attachment in tdf@12089 which have resolved an issue about using
> SUBTOTAL inside SUMPRODUCT function.

Could you provide a link to see the solution ? I didn't found...
Thanks.
Comment 10 Eike Rathke 2021-03-16 11:24:45 UTC
(In reply to Thierry Munoz from comment #7)
> An enhancement is supposed to provide an improvement, a powerful tool.
> So why an other function couldn't be possible ? New function not in others
> programms to avoid conflicts.
> An other solution, perhaps would be to add on 3rd term optional (not
> obligatory). Without this, normal behaviour.

Just to clarify, adding a different syntax or another parameter to an existing standardized function like SUBTOTAL() that is implemented by several applications is a no-go interoperability-wise. The "proprietary" term used here is misleading. Introducing a completely different new function would be fine, but I doubt that's going to happen for this case, since other solutions using already existing functions seem to exist.