IsRowVisible Function

I am creating a report that has filtering on one worksheet and on another it has the sums of those filtered columns.  When chosing a filter, it hides rows.  The sums on the other page do not reflect what has been filtered... It still gives the total sum of all the rows.  

I am trying to mimic the behavior of charts.  If a row is removed from a chart's data range, that row of data is no longer displayed on the chart.  This is what I am trying to accomplish with filters.

I tried doing a macro and calling it from the cell....

Public Function SGM_IsRowVisible(oRange As Range)

    If oRange.Cells.Count = 1 Then
        If oRange.Cells.Height > 0 Then
            SGM_IsRowVisible = True
        Else
            SGM_IsRowVisible = False
        End If
    Else
        SGM_IsRowVisible = "#VALUE!"
    End If
End Function

but the cells formula does not update when the cells are hidden/unhidden and I do have auto-update on.

I know another way to do it, but it is too obscure for users of this workbook to use:

using the formula =SUBTOTAL(3,C4) will return a 1 if the cell is visible and 0 if it is not.

I would like to avoid using macros to update the cells each time a filter is applied.  But using a macro as a custom formula is OK

Does anyone know either 1) How I can get my custom funtion to properly refresh when rows are hidden/unhidden or 2) A formula that makes sense to the user that they can use?

Thanks!