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 FunctionAdditional resources:
