Microsoft Office Tutorials and References
In Depth Information
SUM VISIBLE ROWS
Figure 44. You don’t have to use 109 if your rows are hidden as the
result of a i lter.
Why even mention this strange anomaly? Because there is a little-known
shortcut key to sum the visible rows as the result of a ﬁ lter. Try these steps:
Choose one cell in your data set.
From the Excel 2003 menu, choose Data, Filter, AutoFilter. From the Excel
2007 ribbon, choose Data, Filter. Excel adds dropdowns to each heading.
Open the Customer dropdown. In Excel 2003, choose one customer. In
Excel 2007, uncheck Select All and then choose one customer.
Move the cell pointer to a cell immediately below the ﬁ ltered data. Choose
a cell below one or all of the numeric columns.
Press Alt+= or click the AutoSum icon. Instead of using a SUM function,
Excel uses =SUBTOTAL(9 , which totals only the rows selected by the ﬁ lter
Figure 45. Pressing Alt+= i lls in the SUBTOTAL functions in the selection.