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

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


John Whitworth wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

"John Whitworth" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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

"John Whitworth" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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..

--

Regards,

Peo Sjoblom


"NealUK" <(E-Mail Removed)> wrote in message
news:boe2s0$ihm$(E-Mail Removed)...
> 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
>
> "John Whitworth" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
>
>

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

"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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..
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "NealUK" <(E-Mail Removed)> wrote in message
> news:boe2s0$ihm$(E-Mail Removed)...
> > 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
> >
> > "John Whitworth" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> >
> >
>
>