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.

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.

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.

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.

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 ?