Conditional count in filtered range

Question

I have a sheet with about a million rows. Within one particular column, I have numbers ranging from 0 to 50,000.

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

I can easily do a =COUNTIF(L:L, "<5000") to see how many rows are less than 5,000, or =COUNTIFS(L:L,">500",L:L,"<5000") to see numbers in the TOTAL range that fall between two numbers, but I cannot figure out how to do either of these in a filtered range.

Normally with filtered data I use the =SUBTOTAL function, but I cannot see how any of the established =SUBTOTAL functions would work in this example.

Any ideas?

Answer 1

Here is a VBA solution. I have commented the code so you shouldn't have any problem understanding it but if you do then simply post back.

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'm sure this has happened to everyone, but after reading posts on this topic for like 30 minutes and not finding anything, I posted on SO, only to find a solution like 4 minutes later.......

This solution is for data that's in column L, where the criteria that I'm searching for is "under 5000"

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