How does one sum only those rows in excel not filt

Question

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?

Answer 1

You need to use the SUBTOTAL function. The SUBTOTAL function ignores rows that have been excluded by a filter.

The formula would look like this:

=SUBTOTAL(9,B1:B20)

The function number 9, tells it to use the SUM function on the data range B1:B20.

If you are 'filtering' by hiding rows, the function number should be updated to 109.

=SUBTOTAL(109,B1:B20)

The function number 109 is for the SUM function as well, but hidden rows are ignored.

Answer 2

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.

Answer 3

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.