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

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

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

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  lt  gt  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

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

Examples related to excel

Examples related to excel-formula