count with data filters on

Discussion in 'Microsoft Excel Discussion' started by PeterM, Aug 25, 2008.

  1. PeterM

    PeterM Guest

    Is there a way in Excel 2003 to use one of the count functions on a sheet
    that is using data filters. If I filter a column so that, for example, 10
    rows out of a total of 100 rows are currently displayed, to use a counta or
    countif function to count the rows filtered? In other words, I want the
    count function to return 10, not 100...thanks in advance for your help!
     
    PeterM, Aug 25, 2008
    #1
    1. Advertisements

  2. PeterM

    Ron Coderre Guest

    Try using the SUBTOTAL function.
    The value returned is dependent upon the first argument (parameter)
    in the function.
    Example:
    =SUBTOTAL(9,A2:A100) returns the sum of visible numbers in a filtered range.

    Here are other 1st arugument options:
    Func Num__Function
    1_________AVERAGE
    2_________COUNT
    3_________COUNTA
    4_________MAX
    5_________MIN
    6_________PRODUCT
    7_________STDEV
    8_________STDEVP
    9_________SUM
    10_________VAR
    11_________VARP

    Note: Adding 100 to any of those FuncNums causes the SUBTOTAL function to
    ignore HIDDEN rows, not just hidden FILTERED rows.

    Example: =SUBTOTAL(103,A1:A20) counts non-blank, non-hidden cells.

    Does that help?
    ***********
    Regards,
    Ron

    XL2003, WinXP


    "PeterM" wrote:

    > Is there a way in Excel 2003 to use one of the count functions on a sheet
    > that is using data filters. If I filter a column so that, for example, 10
    > rows out of a total of 100 rows are currently displayed, to use a counta or
    > countif function to count the rows filtered? In other words, I want the
    > count function to return 10, not 100...thanks in advance for your help!
     
    Ron Coderre, Aug 25, 2008
    #2
    1. Advertisements

  3. PeterM

    Don Guillett Guest

    Look in the help index for SUBTOTAL

    --
    Don Guillett
    Microsoft MVP Excel
    SalesAid Software

    "PeterM" <> wrote in message
    news:...
    > Is there a way in Excel 2003 to use one of the count functions on a sheet
    > that is using data filters. If I filter a column so that, for example, 10
    > rows out of a total of 100 rows are currently displayed, to use a counta
    > or
    > countif function to count the rows filtered? In other words, I want the
    > count function to return 10, not 100...thanks in advance for your help!
     
    Don Guillett, Aug 25, 2008
    #3
  4. PeterM

    PeterM Guest

    kewl....thank you very much

    "Ron Coderre" wrote:

    > Try using the SUBTOTAL function.
    > The value returned is dependent upon the first argument (parameter)
    > in the function.
    > Example:
    > =SUBTOTAL(9,A2:A100) returns the sum of visible numbers in a filtered range.
    >
    > Here are other 1st arugument options:
    > Func Num__Function
    > 1_________AVERAGE
    > 2_________COUNT
    > 3_________COUNTA
    > 4_________MAX
    > 5_________MIN
    > 6_________PRODUCT
    > 7_________STDEV
    > 8_________STDEVP
    > 9_________SUM
    > 10_________VAR
    > 11_________VARP
    >
    > Note: Adding 100 to any of those FuncNums causes the SUBTOTAL function to
    > ignore HIDDEN rows, not just hidden FILTERED rows.
    >
    > Example: =SUBTOTAL(103,A1:A20) counts non-blank, non-hidden cells.
    >
    > Does that help?
    > ***********
    > Regards,
    > Ron
    >
    > XL2003, WinXP
    >
    >
    > "PeterM" wrote:
    >
    > > Is there a way in Excel 2003 to use one of the count functions on a sheet
    > > that is using data filters. If I filter a column so that, for example, 10
    > > rows out of a total of 100 rows are currently displayed, to use a counta or
    > > countif function to count the rows filtered? In other words, I want the
    > > count function to return 10, not 100...thanks in advance for your help!
     
    PeterM, Aug 25, 2008
    #4
  5. PeterM

    Gord Dibben Guest

    See Help on the SUBTOTAL funtion.

    =SUBTOTAL(2,range)

    COUNT is 2 or 102 depending upon whether or not you also have hidden rows in
    the range.


    Gord Dibben MS Excel MVP

    On Mon, 25 Aug 2008 13:20:02 -0700, PeterM
    <> wrote:

    >Is there a way in Excel 2003 to use one of the count functions on a sheet
    >that is using data filters. If I filter a column so that, for example, 10
    >rows out of a total of 100 rows are currently displayed, to use a counta or
    >countif function to count the rows filtered? In other words, I want the
    >count function to return 10, not 100...thanks in advance for your help!
     
    Gord Dibben, Aug 25, 2008
    #5
  6. PeterM

    weedfreer Guest

    Now then Ron,

    Is there an easy way (i.e. without the need to program a VBA new row button)
    to make this formular autoupdate if i were to add a new row to the bottom of
    the list?


    --
    Be nice to the geeks as one day you'll probably be working for one!!
     
    weedfreer, Sep 16, 2008
    #6
  7. PeterM

    Ron Coderre Guest

    If you're using Excel 2003...just make your data range a List

    From the Excel Main Menu:
    <data><List><Create List>

    With the formula referencing the list data,
    adding more rows to the list will cause the
    formula reference to change to accommodate
    the new data

    Does that help?

    Regards,

    Ron Coderre
    Microsoft MVP (Excel)

    "weedfreer" <> wrote in message
    news:...
    > Now then Ron,
    >
    > Is there an easy way (i.e. without the need to program a VBA new row
    > button)
    > to make this formular autoupdate if i were to add a new row to the bottom
    > of
    > the list?
    >
    >
    > --
    > Be nice to the geeks as one day you'll probably be working for one!!
    >
    >
    >
     
    Ron Coderre, Sep 17, 2008
    #7
  8. PeterM

    weedfreer Guest

    I am using Excel 2007...what you have said sounds logical but not sure how to
    select that option in 2007. I do need to back save to older version for a
    friend though :)


    --
    Be nice to the geeks as one day you'll probably be working for one!!


    "Ron Coderre" wrote:

    > If you're using Excel 2003...just make your data range a List
    >
    > From the Excel Main Menu:
    > <data><List><Create List>
    >
    > With the formula referencing the list data,
    > adding more rows to the list will cause the
    > formula reference to change to accommodate
    > the new data
    >
    > Does that help?
    >
    > Regards,
    >
    > Ron Coderre
    > Microsoft MVP (Excel)
    >
    > "weedfreer" <> wrote in message
    > news:...
    > > Now then Ron,
    > >
    > > Is there an easy way (i.e. without the need to program a VBA new row
    > > button)
    > > to make this formular autoupdate if i were to add a new row to the bottom
    > > of
    > > the list?
    > >
    > >
    > > --
    > > Be nice to the geeks as one day you'll probably be working for one!!
    > >
    > >
    > >
     
    weedfreer, Sep 17, 2008
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ian

    Filters

    ian, Aug 19, 2003, in forum: Microsoft Excel Discussion
    Replies:
    0
    Views:
    486
  2. Phil C

    Protect a worksheet while leaving data filters functional

    Phil C, Sep 5, 2003, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    517
    Dave Peterson
    Sep 5, 2003
  3. Steve Klenner

    Multiple Auto Filters ?

    Steve Klenner, Mar 29, 2004, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    216
    Dave Hawley
    Mar 30, 2004
  4. Atholl Fitzgerald

    Filters

    Atholl Fitzgerald, May 24, 2004, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    193
    Debra Dalgleish
    May 24, 2004
  5. Atholl Fitzgerald

    Filters

    Atholl Fitzgerald, Jun 15, 2004, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    173
    Debra Dalgleish
    Jun 15, 2004
Loading...

Share This Page