XL98: SUBTOTAL May Include Data in Hidden Rows

• Microsoft Excel 98 Macintosh Edition

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)

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:

   =SUBTOTAL(9, "Area")

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.

STATUS

Microsoft has confirmed this to be a problem of Microsoft Excel 98 for Windows. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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