Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: COUNT formula

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to filter for an item and then count the number of incidents filtered. I know its easy, but. . .it eludes me. TIA

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    subtotal(9,A1:a5)

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Lars, you formula looks like a Row 9 A1:A5 subtotal for numbers. I'm trying to COUNT how many times an "Active" status in a Column occurs in my dbase. Something like =COUNT(D:D) -- but this doesn't work. Am I making myself clear? Hope so.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    don't quite get the 'filter' thing, but how about =COUNTIF(D:D,"Active")?

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-11 16:43, KATHWUN wrote:
    I want to filter for an item and then count the number of incidents filtered. I know its easy, but. . .it eludes me. TIA
    =SUBTOTAL(2,range) [ for COUNT ]

    or

    =SUBTOTAL(3,range) [ for COUNTA ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you filtering it then counting it or just counting it if you just count it it's counta(range)

    or if you filter it then use subtotal(3,range) that will count the number of items in the range

    if there are other items in the range and you want only certain ones counted then use COUNTIF(range,"name")

    Try those

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'll try to make the need clearer:
    STATUS Column D has either "Active" or "Inactive". When I filter the column for just "Active" entrys in a large dBase, I want to count how many "Active" cells are filtered out of the whole dBase.
    So, I want a formula that will count the number of times the word "Active" occurs in the filtered data. The row numbers aren't consecutive, since they've been filtered out of the dBase. Does this help? I hope so.

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My process is to Filter then Count.
    Your suggestion below worked:

    "if you filter it then use subtotal(3,range) that will count the number of items in the range"

    Thanks again, Lars. I'll study the subtotal function further. I don't understand the "3" part.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anno suggested what I was going to suggest.

    =countif(D:D,"active")

    Using D:D means it looks for "active" anywhere in D1:D65536; it'll count all instances of the word "active" whether the filter is on or not).

    _________________
    Regards, Duane

    [ This Message was edited by: Duane on 2002-04-11 18:13 ]

    [ This Message was edited by: Duane on 2002-04-11 18:16 ]

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Several people suggested solutions; what happened when you adjusted to relevant ranges and tried the formula?

    HELP has information on Subtotal

    Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

    Syntax

    SUBTOTAL(function_num,ref1,ref2,)

    Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.

    Function_Num Function
    1 AVERAGE
    2 COUNT
    3 COUNTA
    4 MAX
    5 MIN
    6 PRODUCT
    7 STDEV
    8 STDEVP
    9 SUM
    10 VAR
    11 VARP
    Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •