Worksheet COUNTIF function and AutoFilters

J

John Whitworth

I have set up a worksheet with various columns that can contain "Yes"
or "No" values. I have then set up a totals row at the bottom, that
uses the =COUNTIF(K5:K82,"Yes") function.

What I need to be able to do is then select all the Yes values in any
of the columns, and then have the totals line dynamically update,
counting only the "Yes" values that are still visible.

Thanks

John
 
D

Debra Dalgleish

As answered in .excel:

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 "Yes" cells in column D, after a filter on another
column, you could use the following, where there are no blank cells in
column A:

=SUMPRODUCT((D2:D200="Yes")*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))
 
J

JMay

Why not use the Data-Advanced-Filter with "stair-case criteria" = Y on your
columns
Then Use in K84 =SUMPRODUCT((K5:K82="Y")*1) to get the count of all Y's in
that column. Copy across for others.
HTH
 
D

Dublevay

Debra Dalgleish said:
As answered in .excel:
Click to expand...

Thanks Debra. I was connecting in from work, via both the Microsoft site,
and the Google Groups site, as port 119 is blocked by the firewall. Google
wasn't showing my message, so I tried MS, but for some reason, the MS site
only showed .misc.

I'll check out the link you mentioned.

Thanks again,

John
 
Top