Results 1 to 6 of 6

Thread: Counts / Frequencies / Multiple Criteria’s Problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counts / Frequencies / Multiple Criteria’s Problem

    Hello World,

    I think I have a challenge here, or maybe this is an excel limitation. I need to count the unique Ticket Numbers (Column B) for a given month / date range (Column E), but also segregate it out by Place (Column D) uniquely. The table to the left is the source of the information and variables, while the table on the left with the months and place numbers are the results. I also have excel 2007.

    Thank you and good luck to anyone that can help

    Ticket Number Company Place Start Date End Date Place1 Place2
    1 Company1 Place1 1/1/2015 1/2/2015 January 2 1
    1 Company2 Place1 1/1/2015 1/2/2015 Febuary 1 1
    2 Company3 Place2 1/2/2015 2/1/2015
    3 Company2 Place1 1/2/2015 1/3/2015
    4 Company2 Place1 2/1/2015 2/2/2015
    5 Company2 Place2 2/8/2015 2/9/2015

  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: Counts / Frequencies / Multiple Criteria’s Problem

    What do you have in G2 - a literal January or something like 1-Jan-15 displayed as January?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Feb 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counts / Frequencies / Multiple Criteria’s Problem

    Quote Originally Posted by Aladin Akyurek View Post
    What do you have in G2 - a literal January or something like 1-Jan-15 displayed as January?
    G2 would have all the literal months written out going downward in order, January, February, March...
    However, there are two "tables" here and are separated by the column that has blanks. The table to the left will have all the information needed to get the answers to the table on the right, specifically "H2, H3, I2 and I3" . Here is a link to the same thing that might help when looking at it.

    http://s9.postimg.org/6em1pvxq7/unnamed.png
    Last edited by micdanspe; Feb 6th, 2015 at 11:22 AM. Reason: wording

  4. #4
    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: Counts / Frequencies / Multiple Criteria’s Problem

    H2, control+shift+enter, not just enter, copy across, and down:
    Code:
    
    
    
    =SUM(IF(FREQUENCY(IF($A$2:$A$7<>"",
      IF($C$2:$C$7=H$1,IF($D$2:$D$7-DAY($D$2:$D$7)+1=(1&$G2)+0,
      IF($E$2:$E$7-DAY($E$2:$E$7)+1=(1&$G2)+0,
      MATCH($A$2:$A$7,$A$2:$A$7,0))))),
      ROW($A$2:$A$7)-ROW($A$2)+1),1))
    
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Feb 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counts / Frequencies / Multiple Criteria’s Problem

    Quote Originally Posted by Aladin Akyurek View Post
    H2, control+shift+enter, not just enter, copy across, and down:
    Code:
    
    
    
    =SUM(IF(FREQUENCY(IF($A$2:$A$7<>"",
      IF($C$2:$C$7=H$1,IF($D$2:$D$7-DAY($D$2:$D$7)+1=(1&$G2)+0,
      IF($E$2:$E$7-DAY($E$2:$E$7)+1=(1&$G2)+0,
      MATCH($A$2:$A$7,$A$2:$A$7,0))))),
      ROW($A$2:$A$7)-ROW($A$2)+1),1))
    
    Of course your name is Aladin, because this was magic!
    Thank you so much!

  6. #6
    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: Counts / Frequencies / Multiple Criteria’s Problem

    Quote Originally Posted by micdanspe View Post
    Of course your name is Aladin, because this was magic!
    Thank you so much!
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

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
  •