Auto filtering & COUNTIF

[Guest]

I have a spreadsheet that contains an attendance list of courses over the
last 5 years offered through a healthcare facility. I am applying a filter
that filters by course title, date, and status (pass, fail, cancel...). Is
there a way of setting up a count function to count each cell that contains
the text "pass?" Then I want the formula to be applied to each subsequent
filter. How can I do this?

Thank you

 

[Guest]

Since the auto filter cannot reference an outside cell to set the criteria,
and outside functions cannot read the criteria you have set in the auto
filter, this would require a vba function work-around. I have done this
before. (Vba can set the criteria of autofilter)

I just tested the advanced filter function. It does reference outside cells
for criteria, so you can use it in conjunction with countif or sumif type
functions (which also can use the content of outside cells as criteria) to do
what you want, but the advanced filter is cumbersome, it requires multiple
clicks every time you change the criteria. (It doesn't change automatically
when you change the contents of the criteria cells.) So you would still be
looking at some coding to automate that function, just to make it useful.

Are you up to some vba coding?

SongBear

 

[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 sum cells in column E, where column D contain the value
"Pass", after a filter on another column, you could use the
following, where there are no blank cells in those rows in column A:

=SUMPRODUCT((D2200="Pass")*(E2:E200)*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

 

[PC]

Look at SUBTOTAL

PC

 

[Aladin Akyurek]

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 sum cells in column E, where column D contain the value
"Pass", after a filter on another column, you could use the
following, where there are no blank cells in those rows in column A:

=SUMPRODUCT((D2200="Pass")*(E2:E200)*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

[...]

Can be a bit shorter and faster:

=SUMPRODUCT(--(D2200="Pass"),(SUBTOTAL(9,OFFSET(E2,ROW(E2:E200)-ROW(E2),,1))))

Caveat. This shortened up version skips the range to which the filter is
applied. It will thus not take into account the empty cells in the
skipped range, while the formula you suggest would if A2:A200 is the
range the filter is applied.