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

=subtotal(function_num,ref1,...)

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

Gallery

stars (0 Reviews)

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

Website designed by Astrava Solutions Ltd.