Remember Me?

#1
May 19th 05, 10:20 PM
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

#2
May 19th 05, 11:11 PM
 SongBear Posts: n/a

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

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

#3
May 20th 05, 12:21 AM
 Debra Dalgleish Posts: n/a

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

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

#4
May 20th 05, 01:32 AM
 PC Posts: n/a

#5
May 20th 05, 10:06 AM

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post maxtrixx Excel Discussion (Misc queries) 5 March 31st 05 06:21 PM Mikey Boy Excel Worksheet Functions 0 December 7th 04 11:53 PM Patricia Martinez Excel Worksheet Functions 3 November 28th 04 02:00 AM maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM KC Rippstein Excel Worksheet Functions 1 October 28th 04 06:13 PM

All times are GMT +1. The time now is 02:34 AM.