Sum visible rows in a filtered list

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

How to sum visible rows in a filtered list?

  1. Write the formula in cell B2
  2. SUMVISIBLE(A10:B10)
  3. Press Enter
  4. The function will return rows that are filtered out

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.

Generic formula



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).


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.

SUM visible rows using the 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 to sum values in visible rows and ignore values hidden in the selected list.

Ignore filtered rows

Add “9” as a first argument to sum visible rows and ignore hidden rows:

ignore filtered rows


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 returns 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.


=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.


=SUBTOTAL(9, 5, D3:D12)

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.

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.