Hello,
I am trying to get the total number of cells greater (and equal) than zero in a filtered list.
I know that I can do that with countif in an unfiltered list and I know that I can count all not blank cells with sumtotal.
But does anybody know how I can get the number of cells greater (and equal) than zero in a filtered list?
I really cannot figure it out.
Thank you very much in advance!
kally
Find attached a sample file on how to do, with several ways to get it.
Some of them are matrix formulas introduced with [Ctrl+shift+Enter], other use SUMPRODUCT() function to force the formula as a matrix.
Hello,
thank you for your quick reply! This is very interesting and I think I will need that too, but this is not what I am needing right now. If you eg. have positive and negative values in your column J, I want to count how many cells are positive or negative after I have applied a filter. I don’t want to create the sum of those values.
Sorry for the misunderstanding! Thank you very much in advance! kally
As far as I know, only the the numbered functions 1,2,3 and 101,102,103 usable with SUBTOTAL() regard whether the rows are filtered (out!) or invisible due to hiding respectively. These functions use AVERAGE(), COUNT() and COUNTA() in this order. The term “filter” only occurs in 6.16.60 SUBTOTAL in all the OpenDocument-v1.3-cs01-part4-formula document specifying the Calc functions alongside with other topics.
However, it is simple to determine “filtered” (not out) and “visible” by user code, in specific if a range can be passed based on on the module option VBAsupport 1. The following function is a generic array-function.
Option VBAsupport 1
Function isApplicableRow(pRange As Object, PMode As String)
REM pMode: "f" NOT filterd out, "v" viisible
REM The usage of the term "filtered is ambiguous.
REM LibO API tells IsFiltere=True if the row is filtered OUT.
REM Common usage seems to prefer the opposite.
REM Here "f" addresses rows which passed the filter.
REM The range may have an arbitrary number of columns. Only rows apply.
rs = pRange.CellRange.Rows
u = rs.Count - 1
Dim out(u, 0) As Boolean
For j = 0 To u
Select Case Ucase(pMode)
Case "F"
out(j, o) = NOT rs(j).IsFiltered
Case "V"
out(j, 0) = rs(j).IsVisible
Case Else
REM No action
End Select
Next j
isApplicableRow = out
End FunctionHello Lupp,
thank you for your response.
I don’t want to use macros or VBA for this sheet.
Isn’t it possible without them, even with an extra “temp” sheet?
Would this even be possible in Excel?
Thank you very much in advance.
kally
I don’t know anything about recent Excel, and when I sometimes used it decades ago I wasn’t concerned about questions of the kind. In fact I never used SUBTOTAL() in a sheet for real application. Don’t like it.
That it might allow a sneaky workaround for the missing ISFILTERED() function was just a hunch, and I didn’t want to work it out.
If you want to do me a favor, consider to read what I wrote here about parameter separators and localization.
I got it:
my unfiltered data is in the cells P24:P640
here is the code:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(P24,ROW(P24:P640)-ROW(P24),0)),(P24:P640>=0)+0)
Thus I am getting the number of filtered cells which are greater or equal to 0.
But now I got another question:
I need to add another criteria to the upper code. I need to count the number of cells which are greater than zero AND have the text “x” in another corresponding column.
How do I have to adjust the upper code to achieve this?
Thank you very much in advance!
kally
edit: got it too 
=SUMPRODUCT(SUBTOTAL(3,OFFSET(P24,ROW(P24:P640)-ROW(P24),0)),(P24:P640>=0),(E24:E640=“x”)+0)