Exceljet

Quick, clean, and to the point

Sum visible rows in a filtered list

Excel formula: Sum visible rows in a filtered list
Generic formula 
=SUBTOTAL(9,range)
Summary 

To sum visible values rows in a filtered list (i.e. exclude rows that are "filtered out"), use the SUBTOTAL function with function number 9 or 109.  In the example shown, the formula in F2 is:

=SUBTOTAL(9,F5:F14)
Explanation 

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

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.