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

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

User · Answer

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.

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to excel-formula