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

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:A2 00,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))


Paul S wrote:
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
Contextures
http://www.contextures.com/tiptech.html