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

Question

The title says it all really - 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

A filter only hides rows from view. That means SUM(B1:B20) will always sum all rows in the range, regardless if some rows are hidden in between. I don't think there even is a function that takes the "visible" status of cells into account.

An alternative would be to use SUMIF() and make that dependent on the adjacent column.

A quick-and-dirty solution would be to place the filter, and recreate the SUM() function over the filtered range. Excel will then create something like this: SUM(B1:B3;B17;B19:B20), which will only include currently visible sub-ranges. If you change the filter, this would again stay the same, of course.

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

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 4

THanks it works fine