Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Total filtered items only

    I have a worksheet which is set up like a data base and has data in each cell. the filter is on and the database can be filtered by various fields. I have a total for the whole data base however, when I filter for certain criteria, I want the total to only sum those items for the criteria selected. I also need a count on another criteria totaling the (criteria) filtered amount only.

    Thank you

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Use SUBTOTAL function. Function Num 1 = average, 2 = count
    =subtotal(1, A1:A1000) for average of the filtered values in A1:A1000 or =subtotal(101, A1:A1000) to average filtered and non-hidden items

    =subtotal(2, A1:A1000) for count of the filtered values in A1:A1000 (or = subtotal(102, A1:A1000)...

    The complete list of function numbers
    1 101 AVERAGE
    2 102 COUNT
    3 103 COUNTA
    4 104 MAX
    5 105 MIN
    6 106 PRODUCT
    7 107 STDEV
    8 108 STDEVP
    9 109 SUM
    10 110 VAR
    11 111 VARP

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    To add on to Steve's good advice, and if you are working with Excel 2010, you can also use a brand new function called: Aggregate.

    The Excel AGGREGATE function returns an aggregate in a list or database. The function can apply different aggregate calculations to a list or database with the option to specify whether hidden rows, error values and/or any nested Subtotal or Aggregate functions are ignored. To begin with, AGGREGATE has 19 functions, compared to SUBTOTAL’s 11. Another advantage is that AGGREGATE can also ignore other things such as errors and nested SUBTOTAL functions in addition to the hidden data.
    Regards,
    Rudi

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you all

Posting Permissions

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