The SUMVISIBLE function is compatible with all Microsoft Excel versions and replaces the SUBTOTAL function.
SUMVISIBLE is a user-defined function; the Excel function library does not contain the function by default.
The function uses a single, required argument, “range“, which represents the range of cells you want to summarize.
In the example, we hide the 5th and 6th rows manually. The SUMVISIBLE function returns the SUM of the visible rows only.
The function uses the “rng” argument as an input variable to calculate the sum of visible cells in a specified range. After that, it loops through the selected range and checks whether the entire row or column is hidden. If the cell is visible, add the value to the “sum” variable until it reaches the last row in the range.
Use the following code or download the example Workbook. To insert a code into a new Workbook, open the VBA editor using the Alt + F11 keyboard shortcut. Add a new module the copy and paste the code below.
Function SUMVISIBLE(rng As Range) As Double Dim cell As Range Dim sum As Double sum = 0 For Each cell In rng If Not cell.EntireRow.Hidden And Not cell.EntireColumn.Hidden Then sum = sum + cell.value End If Next cell SUMVISIBLE = sum End Function