XL97: SUBTOTAL May Include Data in Hidden Rows (177051)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q177051

SYMPTOMS

The SUBTOTAL worksheet function may include data from hidden rows when the subtotal is calculated. This behavior occurs even when the data is hidden when you filter the list.

CAUSE

This behavior occurs when you use defined names instead of cell references to describe the range for the SUBTOTAL function. For example, in the following formulas

=SUBTOTAL(9, mary:joe)

-or-

=SUBTOTAL(9, B2:Joe)

"Joe" and "Mary" are defined names that each refer to a cell.

NOTE: This problem will also occur if you use the INDRIECT function to refer to the starting point or ending point of a cell range.

NOTE: When you use a defined name that references the entire range, the problem does not occur. For example, when you use a defined name called "Area" that references a cell range, such as B2:B8, the following formula

=SUBTOTAL(9, "Area")

does not exhibit the problem.

This problem does not occur in earlier versions of Microsoft Excel.

WORKAROUND

To work around this problem, do either of the following:

  • Use a defined name in the SUBTOTAL function only when it refers to the entire range.

    -or-
  • Use cell references instead of defined names to specify the range in the SUBTOTAL function.

STATUS

Microsoft has confirmed that this is a problem in Microsoft Excel 97. This problem no longer occurs in Microsoft Excel 2000.

REFERENCES

For more information about the SUBTOTAL worksheet function, click the Index tab in Microsoft Excel Help, type the following text

SUBTOTAL worksheet function

and then double-click the selected text to go to the "SUBTOTAL" topic.
Modification Type:MajorLast Reviewed:4/21/2001
Keywords:kbbug KB177051
©2002 Microsoft Corporation. All rights reserved.