Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Count if with filters
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count if with filters

    I have a worksheet with 19 columns and, at the top, I have a summary table with "Countif" statements. I have added filters and I want the "countif" results to change depending on how the user changes the filter on any one of the 19 columns. I know "subtotal" will change depending on visible cells but I cannot get the "Countif" results to change. Is it possible?

    Thanks for any comments. I tried to search the message boards to see if this has come up before but I didn't see a related message.

    Lynn

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Count if with filters

    Care to post one of the CountIf formulas you have?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,396
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Count if with filters

    you can do a subtotal on the count of filtered records but countif will not work (as a subtotal) that is.

    see here for seperate subtotal numbers

    What is Excel SUBTOTAL formula and 5 reasons why you should use it | Chandoo.org - Learn Microsoft Excel Online

  4. #4
    New Member
    Join Date
    Oct 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if with filters

    Quote Originally Posted by Aladin Akyurek View Post
    Care to post one of the CountIf formulas you have?
    Of course. I have a table that looks like this:

    Bucket Weeks # of Assoc % of terms
    1 0-13 =COUNTIF($R$11:$R$85270,H2 J2/$J$7
    2 14-26 =COUNTIF($R$11:$R$85270,H3 J3/$J$7
    3 27-39 =COUNTIF($R$11:$R$85270,H4 J4/$J$7
    4 40-52 =COUNTIF($R$11:$R$85270,H5 J5/$J$7
    5 > 52 =COUNTIF($R$11:$R$85270,H6 J6/$J$7
    Total XXXXXX

  5. #5
    New Member
    Join Date
    Oct 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if with filters

    Opps, it came out all run together. The count if statement is simply =countif($R$11:$R$85270,H2)

  6. #6
    New Member
    Join Date
    Oct 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if with filters

    Thanks, yes I figured the countif formulat would not work like the subtotal with the filters. Is there a way around it or another function I can use?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Count if with filters

    Quote Originally Posted by lcohenvb View Post
    Of course. I have a table that looks like this:

    Bucket Weeks # of Assoc % of terms
    1 0-13 =COUNTIF($R$11:$R$85270,H2 J2/$J$7
    2 14-26 =COUNTIF($R$11:$R$85270,H3 J3/$J$7
    3 27-39 =COUNTIF($R$11:$R$85270,H4 J4/$J$7
    4 40-52 =COUNTIF($R$11:$R$85270,H5 J5/$J$7
    5 > 52 =COUNTIF($R$11:$R$85270,H6 J6/$J$7
    Total XXXXXX
    Try...

    =SUMPRODUCT(SUBTOTAL(3,OFFSET($R$11,ROW($R$11:$R$85270)-ROW($R$11),0)),--($R$11:$R$85270=H2))
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    New Member
    Join Date
    Oct 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if with filters

    Wow, thanks, it's doing something but I am trying to figure out exactly what it is doing! I was thinking if I had no filters, it should be the same as my count if but it isn't....

  9. #9
    New Member
    Join Date
    Oct 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if with filters

    Unbelievable, it works! I can't believe how someone on this board can always help me so quickly. Thank you very much!

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Count if with filters

    Quote Originally Posted by lcohenvb View Post
    Unbelievable, it works! I can't believe how someone on this board can always help me so quickly. Thank you very much!
    You are welcome. Thanks for providing feedback.
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •