Excel Function Friday: Sum Filtered List With SUBTOTAL
The Excel SUM function does a great job of adding numbers on a worksheet, and it's probably the first Excel function that you learned about.
But it doesn't do such a great job if you've hidden some of the rows with a filter, or with the Hide Rows command. Even though some of the numbers are hidden, they're still included in the total.
Ignore Filtered Numbers
With the SUBTOTAL function, you can solve that problem, and only sum the visible numbers. There are a couple of quirks with SUBTOTAL though, including one strange thing that I hadn't noticed before.
After you filter the rows in a list, use the AutoSum button on the Excel Ribbon's Home tab, to insert a sum function.
If the table has a filter applied, the SUBTOTAL function will be automatically inserted, instead of the SUM function.
When numbers are hidden with the filter, the SUBTOTAL function sums only the numbers in the visible rows.
If you hide rows by using the Hide Row command, those amounts might be included in the total, even if you use the SUBTOTAL function. In the screen shot below, row 3 is hidden, but the SUM and SUBTOTAL functions both show a total of 40.
The first argument in the SUBTOTAL function specifies which function should be used. As you can see in the list before, there are 11 functions, listed in alphabetical order, starting with 1-AVERAGE.
In Excel 2003, and later versions, the same set of functions is repeated, starting with 101 – AVERAGE.
If you use the arguments in the 100s, neither filtered rows nor manually hidden rows are included in the result.
In the example where row 3 has been manually hidden, if the first argument is changed from 9 to 109, the manually hidden amounts are not included in the SUBTOTAL result. In cell E7, the result is 30, because the 109 argument has been used.
In the example below, there is a filter applied, to hide the Pen Sets item. Then, row 4 was manually hidden. In theory, the SUBTOTAL(9 and SUBTOTAL(109 should return different results.
In this case, the SUBTOTAL functions in columns D and E both return the same result. Even though one row has been manually hidden, the SUBTOTAL function in D7 recognizes it as a filtered row, because it is hidden in a filtered list.
Another "feature" of filtered lists is that if you manually hide rows, they can be automatically unhidden, if you clear the filter. Excel doesn't seem to distinguish between the two types of non-visible rows.
The final quirk, that I just discovered, is that a table below the filtered list is also affected by the filter status. In this example, the upper list is filtered, and a row is manually hidden in the table below. However, the SUBTOTAL(9 formula treats that hidden row as filtered, and doesn't include it in the result. Strange!
Hidden Columns Are Included
Remember that only the Hide Rows command affects the SUBTOTAL result, not the Hide Columns command. Cells hidden with the Hide Columns command are included in the result, as Dick Kusleika mentioned in the comments in his Determine If Cell is Hidden in VBA blog post.
Other SUBTOTAL Oddities
Have you encountered any other SUBTOTAL oddities and quirks?