HOW TO: Use the SUBTOTAL Function with AutoFiltered Lists in Excel 2000

Article translations Article translations
Article ID: 244789 - View products that this article applies to.
This article was previously published under Q244789
Expand all | Collapse all

On This Page

SUMMARY

This article describes how to use the SUBTOTAL function with AutoFiltered lists in Microsoft Excel 2000.

You can use the SUBTOTAL function to dynamically count or display other information about records in a list that has AutoFilter turned on. When you change the AutoFilter criteria, the results of the SUBTOTAL function also change.

Count the Total Number of Filtered Records

To determine the total number of filtered records that the AutoFilter feature returns, use the SUBTOTAL function with the following two arguments:
=SUBTOTAL(function_num,ref)
  • Function_num corresponds to the number of the function that you use to calculate the subtotal:
     1  AVERAGE 
     2  COUNT 
     3  COUNTA 
     4  MAX 
     5  MIN 
     6  PRODUCT 
     7  STDEV 
     8  STDEVP 
     9  SUM 
    10  VAR 
    11  VARP 
    					
  • Ref is the cell range that you want to subtotal.

Count Records in a Filtered List

The following example illustrates how to use the SUBTOTAL function to count records in a filtered list:
  1. Type the following data in a new, blank worksheet:
           A1:  Animal     B1:  In Stock         C1:  Price
           A2:  Dog        B2:  1                C2:  $1.00
           A3:  Cat        B3:  2                C3:  $2.00
           A4:  Dog        B4:  3                C4:  $3.00
           A5:  Cat        B5:  4                C5:  $4.00
           A6:  Bird       B6:  5                C6:  $5.00
    					
  2. On the Data menu, point to Filter, and then click AutoFilter.

    Excel turns on the AutoFilter, and places a drop-down arrow next to each field (column heading).
  3. In cell C8, type the following formula:
    =SUBTOTAL(3,C2:C6)
  4. Click the arrow for the Animal field, and then click Cat.

    The AutoFilter displays all the records that contain Cat in the Animal field. Because two records are now displayed, the subtotal value in cell C8 is 2.
  5. Click the arrow for the Animal field, and then click Bird.

    The AutoFilter displays all the records that contain Bird in the Animal field. Because one record is now displayed, the subtotal value in cell C8 is now 1.

REFERENCES

For more information about the SUBTOTAL function, click Microsoft Help on the Help menu, type subtotal worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

You can also use a macro to get a similar result. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
213275 XL2000: Macro to Count the Number of Records Returned in an AutoFiltered List

Properties

Article ID: 244789 - Last Review: November 5, 2003 - Revision: 4.0
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbhowto kbhowtomaster KB244789

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com