Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Is there any forula/macro that woiuld total just those items that are the result of an auto filter?
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [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?
    Thanks in advance.[/quote]
    Try
    =subtotal(109,range)

    as in this spreadsheet.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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?
    Thanks in advance.[/quote]
    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. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [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
  •