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:
Created attachment 161408 [details] Exemple to understand the enhancement request
See the attachment file
Is this something that other spreadsheet applications offer?
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
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
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.
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.
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.
(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.
(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.
(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 give a link to see this example, please ? Regards, Thierry
Sorry @Thierry in comment#8 I gave a wrong bug number, the correct one is https://bugs.documentfoundation.org/show_bug.cgi?id=120895
Thanks m.a.riosv !