XL2002: AutoSum Button Unexpectedly Returns Values from Hidden Cells in List with AutoFilter

In Microsoft Excel, when you use the AutoSum button, values that have been hidden by an applied AutoFilter are unexpectedly included in the result of the formula.

This behavior can occur if al of the following conditions are true:

• You use the AutoSum button to apply one of the following functions to analyze your list:
  • AVERAGE
  • COUNT
  • MAX
  • MIN
• The list you are analyzing is a range that has an AutoFilter.

You may expect these functions to apply only to the visible rows of your list because when you use the AutoSum button to apply a SUM function to an a range that has an AutoFilter, the AutoSum button actually inserts a SUBTOTAL function with the function parameter of "9" (SUM) to the list. The SUBTOTAL function always calculates visible cells only.

For example, the following formula subtotals only visible cells of a list with an AutoFilter on it.

The other functions on the new AutoSum button may also insert a SUBTOTAL function with the appropriate function parameters. Only the SUM function on the AutoSum button inserts a Subtotal function.

To work around this issue, follow these steps:

1. On the standard toolbar, click the arrow on the AutoSum button.
2. Click Sum.
3. Edit the resulting SUBTOTAL function and replace the parameter "9" with the appropriate function parameter.

NOTE: You are not limited to these function parameters. The following list shows all the function parameters that can be used with the SUBTOTAL function:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Excel 2002 includes new functionality for the AutoSum button, where a drop-down list allows you to select the functions to analyze your list. Those functions include the following:

• AVERAGE
• COUNT
• MAX
• MIN

These functions behave differently than the SUM function when they reference a list with an AutoFilter on it.

When you use the AutoSum button to show the sum of values in such a list, the function =SUBTOTAL(9,ListRange) is inserted in the worksheet, where ListRange is the range of cells whose values that you want to sum. The SUBTOTAL function ignores values in rows hidden by an AutoFilter. If you use the AutoSum button to enter results other than SUM, the function does not insert a SUBTOTAL function in place of the standard function. For example, if you select Average in the AutoSum button drop-down list, the inserted function is =AVERAGE(ListRange). The AVERAGE function, like the COUNT, MAX, MIN, and SUM functions, includes values from hidden cells in the result.

NOTE: This behavior does not apply to lists or ranges that include rows or columns manually hidden by using the Hide command in the Row or Column options on the Format menu. In that case, by using the AutoSum button, you insert the SUM function rather than the SUBTOTAL function into the worksheet. All values in the range, whether hidden or visible, are included in the aggregate result. Furthermore, the SUBTOTAL function ignores only hidden data in lists that have an AutoFilter, or lists that have been subtotaled.

For more information about the SUBTOTAL worksheet function, click Microsoft Excel Help on the Help menu, type subtotal in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.