Conditional account in the filtered range

Question

I have a sheet with a million lines. Inside one column, I have numbers from 0 to 50,000 .

I am trying to determine, within the filtered range, how many cells in the filtered range fall into a specific value.

I can easily do =COUNTIF(L:L, "<5000") to see how many lines are less than 5000 or =COUNTIFS(L:L,">500",L:L,"<5000") to see the numbers in the TOTAL range, which fall between two numbers, but I can’t figure out how to do this in the filtered range.

I usually use the =SUBTOTAL function with filtered data, but I don’t see how any of the installed =SUBTOTAL functions will work in this example.

Any ideas?

Answer 1

Here is the VBA solution. I commented on the code, so you should not have trouble understanding it, but if you do, just send a message.

 Sub Sample() Dim ws As Worksheet Dim lRow As Long, n As Long Dim rng As Range, rngArea As Range '~~> Change this as applicable Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Finding last row in Col L lRow = .Range("L" & .Rows.Count).End(xlUp).Row - 1 'Debug.Print Intersect( _ .Range("L2:L" & lRow), _ .Range("L1").Offset(1, 0).SpecialCells(xlCellTypeVisible) _ ).Address '~~> This is your range of all visible cells till the last row in column L '~~> except the header: Set rng = Intersect( _ .Range("L2:L" & lRow), _ .Range("L1").Offset(1, 0).SpecialCells(xlCellTypeVisible) _ ) '~~> Since the area could be non contiguous we use Countif per area and add up For Each rngArea In rng n = n + Application.Evaluate("=COUNTIFS(" & rngArea.Address & _ ","">500""," & rngArea.Address & ",""<5000"")") Next Debug.Print n End With End Sub 

Answer 2

I am sure that this happened to everyone, but after reading the posts on this topic in 30 minutes and did not find anything, I posted on SO, only to find a solution, like after 4 minutes .......

This is a data solution that is in column L, where the criteria I'm looking for are under "less than 5000"

 =SUMPRODUCT(SUBTOTAL(2,OFFSET(L7,ROW(L7:L999999)-ROW(L7),,1)),--(L7:L999999 < 5000))