Sum visible rows in a filtered list

What makes SUBTOTAL especially useful is that it automatically ignores rows that are hidden in a filtered list or table. You can use this feature when you need to count, sum, average, etc. values that are visible. In other words, you can use SUBTOTAL to perform calculations that exclude "filtered out" values.

The SUBTOTAL function provides many options for calculations, determined by a function number provided as the first argument.

To sum only the visible rows, use the SUBTOTAL function with function number 9 or 109. In the example shown, a filter has been applied to the data, and the goal is to sum the values in column F that are still visible. The formula used is:

=SUBTOTAL(9,F5:F14)

which returns the sum, $9.54, the total for the 7 rows that are still visible.

If you are hiding rows manually (i.e. right-click, Hide), use this version instead:

=SUBTOTAL(109,F5:F14)

Using 109 for the function number tells SUBTOTAL to ignore manually hidden rows.

By changing the function number, the SUBTOTAL function can perform many other calculations (e.g. COUNT, SUM, MAX, MIN, etc.). See the full list of function numbers on this page).