XL98: SUBTOTAL May Include Data in Hidden Rows (180720)
The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q180720 SYMPTOMS
When you calculate a subtotal by using the SUBTOTAL worksheet function,
Microsoft Excel may include data from hidden rows. This behavior occurs
even when you filter the list to hide the data.
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 the cell range B2:B8, the problem does not occur, as
in the following example:
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 a cell reference instead of a defined name to specify the range in
the SUBTOTAL function.
STATUSMicrosoft has confirmed that this is a problem in Microsoft Excel 98 Macintosh Edition. REFERENCES
For more information about the SUBTOTAL worksheet function, click the Index
button in Microsoft Excel Help, and type the following text:
SUBTOTAL worksheet function
and then double-click the selected text to go to the "SUBTOTAL" topic.
| Modification Type: | Major | Last Reviewed: | 9/11/2002 |
|---|
| Keywords: | kbbug KB180720 |
|---|
|