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

1. ## 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.

2. ## 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. ## 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.

4. ## 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. ## 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

6. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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