SUMVISIBLE function

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.

Syntax

=SUMVISIBLE(range)

Arguments

The function uses a single, required argument, “range“, which represents the range of cells you want to summarize.

Example

In the example, we hide the 5th and 6th rows manually. The SUMVISIBLE function returns the SUM of the visible rows only.

Formula:

=SUMVISIBLE(C3:C10)

Explanation:

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.

SUMVISIBLE: Implementation

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

Additional resources:

• How to sum visible rows in a filtered list