PDA

View Full Version : [SOLVED] Countif and Count on visible rows



av8tordude
08-09-2019, 03:24 AM
How can I make this Countif and Count work when rows are filtered?



With WorksheetFunction
CCI = Format(.CountIf(Range("P18:P" & LRow), ">" & 0) / .Count(Range("P18:P" & LRow)), "0%") & " / " & Format(1 - .CountIf(Range("P18:P" & LRow), ">" & 0) / .Count(Range("P18:P" & LRow)), "0%")

End With

p45cal
08-09-2019, 05:37 AM
try:
Set myRng = Range("P18:P" & LRow)
myRngAddr = myRng.Address(external:=True)
k = Evaluate("SUMPRODUCT(SUBTOTAL(3,OFFSET(" & myRngAddr & ",ROW(" & myRngAddr & ")-ROW(" & myRng.Cells(1).Address(external:=True) & "),0,1)),--(" & myRngAddr & ">0))/AGGREGATE(2,5," & myRngAddr & ")")
CCI = Format(k, "0%") & " / " & Format(1 - k, "0%")

av8tordude
08-10-2019, 01:22 AM
Thank you p45cal. Works great!