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

I have been able to get this working by the following:

1) Create three worksheets, one for clients, one for purchases, and one for purchasesforclient.

2) Create a Macro to copy filtered values to a new worksheet:

Sub Purchases()
Dim Rng As Range
Set Rng = Worksheets("Comments").Columns("A")
Set Rng = Rng.Resize(65535, 1).Offset(1, 0)
Set Rng = Rng.Resize(, 5).SpecialCells(xlCellTypeVisible)
Rng.Copy Worksheets("PurchasesforClient").Range("A2")
End Sub

3) When I update the purchases via a filter, I run the macro in step 2 by creating a subtotal field and triggering the macro as follows. Since it is a formula, it requires a calculation to occur. This is embedded in the purchases sheet as VBA where the filtering is occurring, where B23 is the subtotal field that changes when it counts the amount of items once a filter is applied:

Public CurrentValue As Double

Private Sub Worksheet_Activate()
CurrentValue = Application.WorksheetFunction.Sum(ActiveSheet.Range("B23"))
End Sub

Private Sub Worksheet_Calculate()
If Application.WorksheetFunction.Sum(Range("B23")) <> CurrentValue Then Purchases
End Sub

4) I use the now filtered values in the purchasesforclient worksheet for my index/match formula in the clients worksheet. This allows me to dynamically filter by date, purchase type, etc. and have updated information in the clients worksheet

Answer 2

LondonRob mentioned the SUBTOTAL function. AGGREGATE is a more general function than SUBTOTAL that operates with knowledge of both hidden and filtered cells (there is a difference). They'll do that without addins or VBA, though with somewhat hard-to-read formulae.

I learnt it from here.

Answer 3

This answer requires MOREFUNC addon*

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

ARRAY.FILTER() function "Stores only the visible cells of a range (for instance a filtered range) in an array and returns this array. "

---

*MOREFUNC ADDON

• Morefunc Addon is a free library of 66 new worksheet functions.
• HERE is some information (by original author)
• here is the last working download link I found
• here is a good installation walk-through video

Answer 4

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