How to sum only those lines in excel that are not filtered?

Question

I use the SUM(B1..B20) formula SUM(B1..B20) to summarize a column, but when I filter data by a neighboring column, the sum is not updated to reflect only those rows that are not filtered. How to do it?

Answer 1

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

The formula will look like this:

 =SUBTOTAL(9,B1:B20) 

Function number 9 , tells him to use the SUM function in the data range B1: B20 .

If you "filter" by hiding the lines, the function number should be updated to 109 .

 =SUBTOTAL(109,B1:B20) 

Function number 109 is for the SUM function, but hidden lines are ignored.

Answer 2

If you are not using autofilter (i.e. you have manually hidden strings), you will need to use AGGREGATE instead of SUBTOTAL .

Answer 3

When you use an autofilter to filter results, Excel doesn’t even hide them: it just sets the line height to zero (until 2003, at least not sure about 2007).

Thus, the following user-defined function should give you a starter to do what you want (checked by integers, did not play 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 will need to create a module in the book to enable the function, 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.