Exclude Rows that are hidden from SUM

Does anyone know how to exclude a hidden row with a value from the sum of multiple rows?

One option is to use AutoFilter. When you autofilter a range and use the summation key, it automatically sets up a subtotal formula, and this gives you the sum only for visible rows.

Now, this is kind of odd ... I was thinking you could get what you want in a more general case (i.e., a range that is not easy to autofilter to get what you want) using a User-Defined-Function.

This macro works:

Sub testme()
Dim rng As Range
Dim mySum As Variant

    Set rng = Range("B3:D24").Cells.SpecialCells(xlCellTypeVisible)
    mySum = 0
    On Error Resume Next
    mySum = Application.WorksheetFunction.Sum(rng)
    MsgBox mySum
    

End Sub

But, converting this code to a function and calling it from a cell DOES NOT work. Don't know why yet, but it does not work.

Still puzzled why the macro above does not work as a User-Defined-Function (UDF). The problem seems to be in the very first line of code. The visible cells collection seems to lose its mind in a UDF.

Here is something that will work as as UDF. So, if you put this code into a Module, then in a cell you put the formula (for example)
=visibleSum(B3:D22)
it will return the correct answer as you hide and unhide rows.

Public Function visibleSum(Target As Range)
Dim mySum As Variant
Dim c As Range
Dim ws As Worksheet

    On Error GoTo leave
    visibleSum = CVErr(xlErrNA)
    Set ws = Target.Parent
    mySum = 0
    For Each c In Target.Cells
        If Not ws.Rows(c.Row).Hidden Then
            mySum = mySum + c.Value
        End If
    Next c
    visibleSum = mySum

leave:
End Function

Running low on brain power. Might not come up with anything better today.

Bryan, I cannot find any reference to the "109" argument you suggest. When I try using it, I get an error.

The documentation I can find for SUTOTAL says:

SUBTOTAL will ignore any hidden rows that result from a list being filtered.

(emphasis added)

I am using Excel 2002. Is "109" something new in Excel 2003 or 2007?

Ignore last question. I found the answer here: http://www.contextures.com/xlFunctions01.html

Looks like SUMPRODUCT(109,...) is available starting with Excel 2003 (which I have on my other PC, but not this one).