AutoSum Tips 3

< Back to Search results

 Category: [Excel]  Demo Available 

AutoSum Tips 3


Excel Autosum on Filtered Data and Hidden Rows

Excel's AutoSum Function.

Using AutoSum with Filtered Data

You can use AutoSum to add items in an autofiltered list, but it works slightly differently.  If you filter a list and use the  AutoSum function to add the figures, you will notice that the function will use the SUBTOTAL function instead of the SUM function.  Excel can automatically detect that it is adding possibly filtered data and therefore uses SUBTOTAL in place of AutoSum because the syntax of the SUBTOTAL function allows the addition of only rows not hidden by AutoFilter. 

The syntax of SUBTOTAL is written as


 the function_num of 9 tells SUBTOTAL to only add the entries that have not been hidden by AutoFilter.

Using AutoSum with Hidden Rows

There is a difference between hidden rows and filtered rows.  Hidden rows are what a user can hide manually by selecting the entire row and going to Format>Row>Hide.  Filtered rows are the rows hidden when data is filtered down through the use of AutoFilter.

If you hide rows within a table (such as the following screen shot which has rows 6 - 8 inclusive hidden) and you try to add using AutoSum, then the hidden rows will also be included in the range you are adding up.

The only way you can add up and NOT include the hidden rows is to use the SUBTOTAL function, and change the function_num of the SUBTOTAL function from 9 to 109.  You can then add up all rows of data showing, ignoring hidden rows. florida fake id maker

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

Excel AutoFilter
Excel AutoFilters Custom Option
Excel AutoSum
Excel Autosum. Sum up Values in Excel Automatically
AutoSum Tips 2


Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)