Results 1 to 7 of 7

Thread: Table sort

  1. #1
    New Lounger
    Join Date
    Sep 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table sort

    When using the count if formula the totals are in correct. How am I able to have the count if totals changed when utilizing filter.

    Thanks

    Jim
    Attached Files Attached Files

  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
    Checkout the subtotal function. It counts (averages, finds min/max, etc) the items that are displayed in a filter

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter sort

    Hmm !,

    sounds familiar. I'll try.

    Thanks

  4. #4
    New Lounger
    Join Date
    Sep 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data filter sort

    Not sure . . . ., I need to filter then be able to total the Q's etc. How is this done please feel free to make ant adjustments necessary

    James

  5. #5
    New Lounger
    Join Date
    Sep 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,

    I have been away from excel for some time and would like furhter assistance.

  6. #6
    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
    You can Count the Qs without filtering with the formula:
    =COUNTIF(B2:B2431,"Q")

    You can subtotal the displayed values after filtering with:
    =SUBTOTAL(3,B2:B2431)

    The "3" is for COUNTA.

    Steve

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you want to count visible Qs depending on the applied filter, then:
    =SUM(IF(SUBTOTAL(3,OFFSET($B$2:$B$2431,ROW($B$2:$B $2431)-MIN(ROW($B$2:$B$2431)),,1)),IF($B$2:$B$2431="Q",1, 0),0))
    array entered with Ctrl+Shift+Enter.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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