+ Reply to Thread
Results 1 to 7 of 7

Count in filtered list and display results in table on right of data

  1. #1
    Registered User
    Join Date
    03-30-2006
    Posts
    32

    Count in filtered list and display results in table on right of data

    I have a long list of delegates attending functions on different dates and need help with 2 problems:
    A) List has filters by date/venue etc but I will want the table to be visible even when list is filtered.
    B) I am using the following formula to count "=SUMPRODUCT(--($E$3:$E$728="Thursday 3rd September 2009"),SUBTOTAL(3,OFFSET($E$3,ROW($E$3:$E728)-MIN(ROW($E$3:$E$728)),,1)))" and this works but if I filter to another date then 3rd September shows 0.
    Last edited by raydaw; 08-27-2009 at 05:18 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count in filtered list and display results in table on right of data

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

  3. #3
    Registered User
    Join Date
    03-30-2006
    Posts
    32

    Re: Count in filtered list and display results in table on right of data

    Hi
    Sorry! Should have attached a sample as you say. I need the data in L1 to L5 to remain (in it's entirety) even when the list is filtered by date

    I can't seem to add it properly to this post so will add in a new post.
    Last edited by raydaw; 08-27-2009 at 03:42 AM. Reason: Don't think my link is working.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count in filtered list and display results in table on right of data

    You will need to place your table in a section away from that being filtered, altering visibility of a row does just that - ie it can't be applied to some columns and not others, it is in effect an "all or nothing" action - either show the entire row or don't.

  5. #5
    Registered User
    Join Date
    03-30-2006
    Posts
    32

    Re: Count in filtered list and display results in table on right of data

    Thank you very much. However, I now have another little problem. The formula shown in my first post works but if I filter the list to a particular date all the other dates show as 0 - see example attached
    Attached Files Attached Files

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count in filtered list and display results in table on right of data

    Well yes I'm afraid that's what the SUBTOTAL function does - it only looks at visible rows thus if you filter by date the hidden rows are ignored.

    On aside your formula in I16 could be:

    Please Login or Register  to view this content.
    It is also not an Array (per se) so does not need to be confirmed with CTRL + SHIFT + ENTER, ie confirm with Enter as per other normal formulae (ie { } not required)

    Worth pointing out also perhaps that the above formula will be pretty bad performance wise as it is Volatile (see link in sig. for more info) - have you thought perhaps of using a Pivot Table ?

  7. #7
    Registered User
    Join Date
    03-30-2006
    Posts
    32

    Re: Count in filtered list and display results in table on right of data

    Thank you!! I'll need to read through these articles - always learning! I'll try a pivot table - always been meaning to get to grips with them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1