Worksheet COUNTIF function and AutoFilters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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))))
 
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
 
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
 
Back
Top