How to ignore filtered-out data in Excel formula

Question

I am using an Index/Match to get data from a related table to populate in the first table. In my related table I have filtered out values, but the filtered out values are still populating in my first table. If Index/Match is not smart enough to only grab the filtered values, how can I work around this (formula preferred, but VBA acceptable) to get only the filtered values.

Here is my current formula:

=INDEX(Table_owssvr__1[MyValues],MATCH([@[ID]],Table_owssvr__1[ID],0))

Answer 1

You might find the SUBTOTAL function useful, as it only works on visible rows. (Here's some more general discussion about SUBTOTAL)

But if that's not flexible enough for your needs, here's how to check whether a certain cell is filtered out or not.

Using this, I've written a bit of VBA code to sum over a column summing only visible cells. Should be a pretty useful start in doing whatever you need to do.

If summing over the cells is not what you want to do, just change the part indicated in the comments. (Obviously you'd have to change the name of the function from sumFilteredColumn to something else!)

Public Function sumFilteredColumn(startCell As Range)

    Dim lastRow As Long ' the last row of the worksheet which startCell is on
    Dim currentCell As Range
    Dim runningTotal As Long ' keeps track of the sum so far

    lastRow = lastRowOnSheet(startCell)
    Set currentCell = startCell

    ' Loop until the last row of the worksheet
    Do While currentCell.Row <= lastRow
        ' Check currentCell is not hidden
        If Not cellIsOnHiddenRow(currentCell) Then
            ' -------------------------------------------------
            ' Here's where the magic happens. Change this to
            ' change sum to, e.g. concatenate or multiply etc.
            If IsNumeric(currentCell.Value) Then
                runningTotal = runningTotal + currentCell.Value
            End If
            ' -------------------------------------------------
        End If
        Set currentCell = currentCell.Offset(1) ' Move current cell down
    Loop

    sumFilteredColumn = runningTotal

End Function

' return the number of the last row in the UsedRange
' of the sheet referenceRange appears in
Public Function lastRowOnSheet(referenceRange As Range) As Long

    Dim referenceSheet As Worksheet
    Dim referenceUsedRange As Range
    Dim usedRangeCellCount As Long
    Dim lastCell As Range

    Set referenceSheet = referenceRange.Parent
    Set referenceUsedRange = referenceSheet.usedRange
    usedRangeCellCount = referenceUsedRange.Cells.CountLarge

    Set lastCell = referenceUsedRange(usedRangeCellCount)
    lastRowOnSheet = lastCell.Row

End Function

' Is the row which referenceCell is on hidden by a filter?
Public Function cellIsOnHiddenRow(referenceCell As Range) As Boolean

    Dim referenceSheet As Worksheet
    Dim rowNumber As Long

    Set referenceSheet = referenceCell.Parent
    rowNumber = referenceCell.Row

    cellIsOnHiddenRow = referenceSheet.Rows(rowNumber).EntireRow.Hidden

End Function