Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count problem (2003)

    Hi Everyone,

    I am trying to find out how many customers are in the department at any one time, there arrival time being between x and y (15 min blocks) and there departure time being z

    =SUMPRODUCT(($D$6:$D$39>=J$5)*($D$6:$D$39<J$6)) this works for doing a count of customers by 15 min blocks but does not cumulatively count customers who arriaved before and have not yet departed.

    J5 = Arrival Time start range
    J6 = Arrival time end range

    {=SUMPRODUCT(($D$6:$D$39>=J$5)*($D$6:$D$39<J$6)*($ F$6:$F$39>J$5)} - This did not work but column F contains the departure times

    Many thanks
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count problem (2003)

    Could you post a small sample workbook?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count problem (2003)

    Thanks

    Here you go
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count problem (2003)

    In J7:

    =SUMPRODUCT(($D$6:$D$39<=J6)*($F$6:$F$39>=J5))

    This is a 'normal' formula, not an array formula.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count problem (2003)

    Hi,

    Thanks Hans, I was definatly making it more complicated than it needed to be! I had found another formular that worked using a sumproduct + sumproduct but your way is much cleaner, thanks
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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