Formula for filtered worksheet

[Paul S]

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

 

[Debra Dalgleish]

Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site:
http://j-walk.com/ss/excel/eee/eee001.txt

For example, to count "Y" cells in column F, after a filter on another
column, you could use the following, where there are no blank cells in
column A:

=SUMPRODUCT((F2:F200="Y")*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))