[excel] How does one sum only those rows in excel not filtered out?

I use the SUM(B1..B20) formula to sum a column, but when I filter the data by an adjacent column, the sum doesn't update to reflect only those rows not filtered out. How does one do this?

If you aren't using an auto-filter (i.e. you have manually hidden rows), you will need to use the AGGREGATE function instead of SUBTOTAL.

When you use autofilter to filter results, Excel doesn't even bother to hide them: it just sets the height of the row to zero (up to 2003 at least, not sure on 2007).

So the following custom function should give you a starter to do what you want (tested with integers, haven't played with anything else):

Function SumVis(r As Range)
    Dim cell As Excel.Range
    Dim total As Variant

    For Each cell In r.Cells
        If cell.Height <> 0 Then
            total = total + cell.Value
        End If
    Next

    SumVis = total
End Function

Edit:

You'll need to create a module in the workbook to put the function in, then you can just call it on your sheet like any other function (=SumVis(A1:A14)). If you need help setting up the module, let me know.