Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My spreadsheet has hundreds of rows, so I filter on the criterion I want in Col A. Then I want to count how many times each text value occurs in Col B, but not include that value in rows hidden by the filter. I can't see that =COUNTIF() does this, and =SUBTOTAL() seems not to work at all with text strings. Is there a function I can use to make this work? Many thanks, Frank

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I am not sure there is a simple answer to this

    I have a formula in the attached example.
    It counts based upon the data in column A in the Range.
    The count is based upon A matching the entry in D2

    It uses Subtotal 3 to count, and sumproduct with row functions and offset,
    to make sure the count works and is filtered.

    =SUMPRODUCT((SUBTOTAL(3,OFFSET($A$8:$A$108,ROW($A$ 8:$A$108)-ROW($A$8),0,1))),--($A$8:$A$108=$D2))

    See attached for Sum example as well

    [attachment=88307:ConditionalFilteredCount.xls]
    Attached Files Attached Files
    Andrew

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Subtotal should work. If TEXT is the problem it seems that you may be using =Subtotal(2,...) for COUNT, when you want =Subtotal(3,...) for COUNTA. Count ignores Text, CountA does NOT.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Steve, I tried with your suggestion but could not make it work.

    In a column of many rows, the formula relating to col U would be =SUBTOTAL(3,U9:U500,"1.Yes") which produces an error (I also tried with SUBTOTAL(103,U9:U500,"1.Yes") to ignore hidden rows but also produced an error). Maybe subtotal uses text strings differently?

    Using =COUNTIF(U$9:U$500,"2.No") I get the right result until I filter the list, it fails to ignore hidden rows.

    Can we get subtotal to 'count' occurrences of a text string in a cell?

    (this all relates to answers to questions in a quiz - one question per column, filter for members of one team, count how many gave each answer)

    Thanks
    Frank

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Subtotal does not take criteria. Filter column U to only display "1.Yes" and then use.
    =SUBTOTAL(3,U9:U500)

    Otherwise you will have to use either an ARRAY type formula [Count(if(.....)) or a SUMPRODUCT(...) variation] or a COUNTIFS (if you have XL2007) to count with multiple criteria and include what you want to filter on in the criteria (though this will not be LIVE if you filter something more)

    You must decide if you want to calculated based on ALL filtered data or base it on criteria. I think if you want both you will have to create your own function...
    Steve

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thankyou Steve, I now can better see how subtotal works.

    What I need is to filter on a different column, selecting in rows according to team membership, then count how many times my chosen value appears in the other column.

    There will be several chosen values, but I can use a set of rows at the top of my sheet to count each one.

    I'm going to try out the suggestion by AKW as well as some other ideas, and post a sample worksheet to demonstrate my problem.

    Many thanks to all for contributing. Frank

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you are looking for some type of summary table, by various groupings, it may be that a pivot table could fit your needs...

    Steve

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Frank,

    If I understand you correctly what you may be looking for are the database functions, DCOUNTA in particular.

    I've attached a workbook to demonstrate what I think you want. Pay particular attention to how the TEXT criteria values are entered. I always seem to forget how this is done and have to refer back to the help files to refresh my memory. I hope this helps.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Frank,
    Did you try Andrew's suggestion? It will work with filtered data.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to all of you you contributed to this thread - I should have realised at once that I was dealing with a database / datalist, and that the DCountA function was what I needed!
    Due to all your suggestions, I've got it to work using it. Many thanks again, Frank

  11. #11
    New Lounger
    Join Date
    Sep 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    What if the data is in the below format and i have hidden away (NOT FILTERED OUT) row E5 but still is being counted. I just want to count how many "Frank" are there? Can anyone help me urgently? I have included current formula inplace (F7) that is generating result "3" in cell E7. Thanks in advance.

    A B C D E F
    2 0.00 0.00 0.00 0.00 Raiden
    3 0.00 0.00 0.00 0.00 Raiden
    4 0.00 0.00 0.00 0.00 Frank
    6 0.00 0.00 0.00 0.00 Frank
    3 =COUNTIF(E2:E6,"Frank")

  12. #12
    New Lounger
    Join Date
    Sep 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    0.00 0.00 0.00 0.00 Raiden
    0.00 0.00 0.00 0.00 Raiden
    0.00 0.00 0.00 0.00 Frank
    0.00 0.00 0.00 0.00 Frank
    3 COUNTIF(E11:E15,"Frank")

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about a formula like:
    =SUMPRODUCT(SUBTOTAL(103,OFFSET(E2:E6,ROW(E2:E6)-ROW(E2),0,1)),--(E2:E6="Frank"))
    For your 1st post

    And for your second post:
    =SUMPRODUCT(SUBTOTAL(103,OFFSET(E11:E15,ROW(E11:E1 5)-ROW(E11),0,1)),--(E11:E15="Frank"))

    The subtotal works for both filtered and/or hidden rows

    Steve

  14. #14
    New Lounger
    Join Date
    Dec 2012
    Posts
    6
    Thanks
    0
    Thanked 1 Time in 1 Post
    You might also try the COUNTIFS() function. This will allow you to count based on the filter condition in column A and the desired text value in B.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    jackhh,
    COUNTIFS will not ignore hidden rows, whether they are explicitly hidden or hidden by a filter. COUNTIFS (like COUNTIF) will only ignore based on the conditions of the formula.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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