Auto filtering & COUNTIF
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 |
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 "Justin Sadowski" wrote: 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 |
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((D2:D200="Pass")*(E2:E200)*(SUBTOTAL(3 ,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Justin Sadowski wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
Debra Dalgleish wrote:
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((D2:D200="Pass")*(E2:E200)*(SUBTOTAL(3 ,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) [...] Can be a bit shorter and faster: =SUMPRODUCT(--(D2:D200="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. |
All times are GMT +1. The time now is 06:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com