How to SUM visible cells and exclude hidden rows and columns

If you have hidden rows only, you can use SUBTOTAL function. In other cases, you can use Excel macros. Here is a VBA function that do SUM for visible cells only. It doesn’t matter if hidden are rows or columns.

Function SumVisible(CRange As Object)

    Application.Volatile

    TotalSum = 0

    For Each cell In CRange

       If cell.Columns.Hidden = False Then

          If cell.Rows.Hidden = False Then

             TotalSum = TotalSum + cell.Value

          End If

       End If

    Next

    SumVisible = TotalSum

End Function

Example – no hidden cells

Sum range A1:C3 by the SumVisible function.

Example – hidden row and column

SumVisible A1:C3 after hiding row no.2 and B column .

Note: If you hide or unhide rows or columns after you have entered the SumVisible function, you have to recalculate the worksheet. For example press F9 or do some changes in other cells.