How does one sum only those rows in excel not filtered out?
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.
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
.
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.