Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apply formula to filtered data (2003)

    Is there a way to create a formula which will modify it's results as the basis of filtering the data. When I filter the data, the formula results still use the entire data set, not the data that appears on the screen (results of the filter).

    Thanks.

    Larry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Apply formula to filtered data (2003)

    If you want to calculate a count, sum, product, average, minimum or similar, you can use the SUBTOTAL function. For example, to sum a range of numbers:

    =SUBTOTAL(9,A2:A100)

    (9 is the code SUBTOTAL uses for SUM). If you filter the data, the result of SUBTOTAL will be adjusted automatically. See the help for SUBTOTAL to see which calculations are available.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apply formula to filtered data (2003)

    Hi Larry

    You can use the subtotal formula like so:

    =SUBTOTAL(9,$A$2:$A$100)

    adjust the range as necessary, the 9 in the formula is to sum
    Jerry

Posting Permissions

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