Tips & Tricks 153 – Sum only Visible Columns

You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2)

If you hide the columns, the sum stays the same whether columns are hidden or visible.

Unfortunately, Excel doesn't provide any native functionality to accomplish this. This will have to be done through a VBA function. We will write a VBA function "SumVisCols" which can be called like =SumVisCols(A2:F2)

(You can give any range not only A2:F2)

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given and change the bold lines as per your requirement
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

' Vijay A Verma (eforexcel.com)
Function SumVisCols(Rng As Range)
    Dim Cell As Range
    Application.Volatile
    For Each Cell In Rng
        If Cell.EntireColumn.Hidden = False And IsNumeric(Cell) Then
            SumVisCols = SumVisCols + Cell
        End If
    Next Cell
End Function