none
Sum of cells in row where column is not hidden RRS feed

  • Question

  • I'm looking for a dynamic way to sum all cells in a row where the column is not hidden. SUM() function includes the hidden columns and SUBTOTAL only works for summing cells in a column with hidden row.

    I'd like the cell displaying the sum total to change as columns are hidden and made visible.

    Is it possible to do a sumif where Cell("width") <>0?

    Monday, October 8, 2018 5:20 PM

Answers

  • You could use the following VBA function:

    Function SumVisibleCols(rng As Range) As Double
        Dim dblSum As Double
        Dim rngCell As Range
        Application.Volatile
        For Each rngCell In rng
            If Not rngCell.EntireColumn.Hidden And IsNumeric(rngCell.Value) Then
                dblSum = dblSum + rngCell.Value
            End If
        Next rngCell
        SumVisibleCols = dblSum
    End Function

    Use like this:

    =SumVisibleCols(B2:N2)

    Remark: the formula result will be updated automatically when you change the value of one of the cells in the range, but not when you hide or unhide columns. You can recalculate the formula yourself by selecting the cell(s) with the formula and pressing F9. It will be updated automatically whenever Excel performs a calculation.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JHarding08 Monday, October 8, 2018 9:04 PM
    Monday, October 8, 2018 7:30 PM