I'm using excel 2000

I need a formula that will count the number of times Y appears in the range F4:F1000, but will change to reflect the number visible when the list is filtered

I tried =COUNTIF(F4:F1000,"Y"), which returns the correct value, however when I filter the list the value from the above formula does not change

I have tried =SUBTOTAL(3,F4:F1000) but this counts all entries in the range, not just the Y's

Any help greatly appreciated