Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 19th 05, 10:20 PM
Justin Sadowski
 
Posts: n/a
Default 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   Report Post  
Old May 19th 05, 11:11 PM
SongBear
 
Posts: n/a
Default

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

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

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


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

  #5   Report Post  
Old May 20th 05, 10:06 AM
Aladin Akyurek
 
Posts: n/a
Default

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining IF and COUNTIF based on two columns maxtrixx Excel Discussion (Misc queries) 5 March 31st 05 06:21 PM
excel links update not working in auto, calculations in auto Mikey Boy Excel Worksheet Functions 0 December 7th 04 11:53 PM
Filtering Data with a Drop Down field Patricia Martinez Excel Worksheet Functions 3 November 28th 04 02:00 AM
Countif - Countif maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM
Why can't my macro use Auto Filter when I told the Sheet Protecti. KC Rippstein Excel Worksheet Functions 1 October 28th 04 06:13 PM


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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017