Microsoft KB Archive/136521

= Result of SUBTOTAL Function Incorrect with Filtered List =

Article ID: 136521

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q136521



SYMPTOMS
When you want to use the SUBTOTAL function to find the subtotal of only the visible data in a filtered list, you may not receive the results you expect if the SUBTOTAL function is located on a different worksheet than the filtered list.



WORKAROUND
To work around this problem, use the SUBTOTAL function in a cell on the same sheet as the list and reference this cell from a cell on a different worksheet. For example, if you want to return the subtotal of the filtered cells in Sheet1!B1:B5 on a different sheet, do the following:

 Activate Sheet1 (the sheet that contains the list).  In cell C1, enter the following formula: =SUBTOTAL(9,B1:B5)  Click cell C1, and click Copy on the Edit menu. Click the cell in which you would like to display the subtotal and click Paste Special on the Edit menu. Click the Paste Link button in the dialog box.



STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



MORE INFORMATION
In Microsoft Excel, you can use the SUBTOTAL function to return a subtotal in a list or database. The SUBTOTAL function should ignore any hidden rows that result from a filtered list.

Additional query words: 5.00a 5.00c subtotals sum XL

Keywords: kbbug KB136521

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.