XL: Result of SUBTOTAL() Function Incorrect with Filtered List

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

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:

1. Activate Sheet1 (the sheet that contains the list).



2. In cell C1, enter the following formula:

   
         =SUBTOTAL(9,B1:B5) 




3. Click cell C1, and click Copy on the Edit menu.



4. 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

Keywords          : xlformula 
Version           : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS 
Issue type        : kbbug 

Last Reviewed: July 29, 1999