Sum visible rows in a filtered list

ignore-manually-hidden-rows

To SUM visible rows in a filtered list in Excel, you can use the built-in SUBTOTAL or AGGREGATE functions or the SUMVISIBLE function.

How to sum visible rows in a filtered list?

Steps to sum visible rows using a filtered list:

  1. Open Excel.
  2. =SUBTOTAL(109, D3:D12).
  3. Press Enter.
  4. The formula will ignore the manually hidden rows and return the sum of values.

SUBTOTAL function

The SUBTOTAL function uses two arguments to perform various actions (SUM, COUNT, AVERAGE, MAX, MIN).

Generic formula:

=SUBTOTAL(function_num, reference1, [reference2]…)

SUBTOTAL can be useful for summarising values in visible rows and ignoring values hidden in the selected list.

Ignore filtered rows

=SUBTOTAL(9,D3:D12)

ignore-filtered-rows

Explanation: In the example, add “9” to the first argument (function_num); this number equals the SUM function in Excel. Then, use the D3:D12 range for the second argument (reference). Finally, apply a filter using the header to hide the values in rows 10 and 11. The SUBTOTAL function will ignore the filtered rows in a range and return with 22000; in other words, sum the visible rows only.

Ignore manually hidden rows

In the example, we hide rows 10 and 11 manually. In this case, use “109” as function_num. Next, select the D3:D12 range as a reference.

Formula:

=SUBTOTAL(109, D3:D12)

The SUBTOTAL function will ignore the manually hidden rows in a range and return with 22000; in other words, it will only sum the values in visible rows.

ignore-manually-hidden-rows

Note: if you want to sum visible rows in a range that contains manually hidden rows, do not use “9”. Always use “109” to get the proper result.

SUM visible rows with the AGGREGATE function

Let us see another method using the built-in AGGREGATE function and apply it to a filtered range.

Formula:

=AGGREGATE(9, 5, D3:D12)

It is good to know the difference between the AGGREGATE and SUBTOTAL functions. By default, SUBTOTAL ignores values in rows hidden by a filter, and you need to use the “109” function number to ignore manually hidden rows.

three-functions-to-sum-visible-rows-in-Excel

AGGREGATE uses three required arguments and ignores the rows that are manually hidden. Furthermore, you must use an additional argument to specify the criteria.

SUMVISIBLE Function

The SUMVISIBLE function returns the sum of visible cells in a filtered list, even if some rows are manually hidden. In the next section, you will learn how the formula works.

Formula:

=SUMVISIBLE(range)

In the example, we have a filtered list. Furthermore, we hide some rows manually to demonstrate how the function works. The SUMVISIBLE function returns the SUM of the visible rows only. The main advantage of the function is that it uses a single argument.

We created a custom data set containing a manually hidden row (6) and rows filtered out using the header filter (rows 10 and 12).

Formula:

=SUMVISIBLE(D3:D12)

sum-visible-rows-in-a-filtered-list

Note: the function is more user-friendly than other Excel functions; select the filtered range, and you will get the result quickly.

You can implement the function manually, but the best practice is to use our free function library, DataFX.

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