COUNTIF and Autofilter

[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, using the AutoFilter dropdown boxes, and then have the
totals line dynamically update, counting only the "Yes" values that
are still visible.

Is this possible?

Thanks

John

 

[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 "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((D2200="Yes")*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

 

[NealUK]

If you insert a line between the last yes/no and the total, then when you
use the auto filter the total doesn't disappear.
Is this what you are after?
regards
Neal

 

[NealUK]

Sorry, just realised what you are after.
How about this?

In A1:A25 you have either yes/no. In B1:B25 you have the values.

In B27 have =SUMIF(A1:A25,"yes",B1:B25)

This will total up all the values in b1:B25 that have a yes value in A1:A25

Hope that is of use
regards
Neal

 

[Peo Sjoblom]

I believe the OP had several column that would filter on yes, your formula
only works if there is one yes column. However in this case if the values
are in one column one can use the subtotal function that works with visible
rows
when filtered. Either =SUBTOTAL(3,Range) which will count cells that are not
blank,
replace 3 with 2 and it counts cells with numeric values and replace with 9
and it will sum the
range..

 

[NealUK]

This is very true, i had indeed misread the post (twice).
And i've learned something to boot as well