Bug 133506 - Subtotal function to obtain the number of particular non numeric values
Subtotal function to obtain the number of particular non numeric values
 Status: RESOLVED WONTFIX None LibreOffice Unclassified Calc (show other bugs) 6.4.4.2 release All All medium enhancement Not Assigned needsUXEval Function-Subtotal Show dependency tree / graph

 Reported: 2020-05-29 21:29 UTC by Thierry Munoz 2021-03-16 11:24 UTC (History) 3 users (show) libreoffice-ux-advise miguelangelrv thierry.munoz

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.
 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:``` Thierry Munoz 2020-05-29 21:30:58 UTC ```Created attachment 161408 [details] Exemple to understand the enhancement request``` Thierry Munoz 2020-06-13 06:17:45 UTC `See the attachment file` Buovjaga 2021-03-11 08:28:57 UTC `Is this something that other spreadsheet applications offer?` 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` 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``` 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(,) would be special, however. We cannot implement functionality that wouldn't work with other programs, therefore WF. If you disagree, feel free to reopen.` 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.``` 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.` 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.``` 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.```