Thread: Subtotal Auto Filter

1. Is there any forula/macro that woiuld total just those items that are the result of an auto filter?

2. [quote name='jlkirk' post='769625' date='07-Apr-2009 23:07']Is there any forula/macro that woiuld total just those items that are the result of an auto filter?
Try
=subtotal(109,range)

as in this spreadsheet.

3. [quote name='jlkirk' post='769625' date='08-Apr-2009 00:07']Is there any forula/macro that woiuld total just those items that are the result of an auto filter?
Yes, the SUBTOTAL function:

=SUBTOTAL(9,A2:A100)

will sum the filtered cells in A2:A100.

Note: 9 is the code for SUM. You can use the following values in the first argument of SUBTOTAL:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

In Excel 2003 and later, you can add 100 to these values if you also want to exclude values that have been hidden manually (not as the result of a filter), e.g.

=SUBTOTAL(109,A2:A100)

4. [quote name='HansV' post='769629' date='07-Apr-2009 17:27']Yes, the SUBTOTAL function:

=SUBTOTAL(9,A2:A100)

will sum the filtered cells in A2:A100.

Note: 9 is the code for SUM. You can use the following values in the first argument of SUBTOTAL:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

In Excel 2003 and later, you can add 100 to these values if you also want to exclude values that have been hidden manually (not as the result of a filter), e.g.

=SUBTOTAL(109,A2:A100)[/quote]

Thanks Hans

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•