Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

AutoSum Tips 3

| | Information Helpful? Why Not Donate.

Excel Autosum on Filtered Data and Hidden Rows

Excel's AutoSum Function. See Also: Excel AutoSum || AutoSum Tips 2

Using AutoSum with Filtered Data

You can use AutoSum to add items in a 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.

See Also: a href="autosum.htm">Excel AutoSum || AutoSum Tips 2

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

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