+ Reply to Thread
Results 1 to 3 of 3

complex count formula?

  1. #1
    Registered User
    Join Date
    07-01-2013
    Location
    South Dakota
    MS-Off Ver
    Excel 2003
    Posts
    2

    complex count formula?

    The data I have is very basic:

    Date || Military Hour of Call (0-23)

    I need to calculate the number of times the number of calls exceeded 2 (occurences) in any hour interval and graph it by day of the week and hour.

    I've not been able to find the calculation that would tell me if "on x day at y hour there was more than one call which occured" AND only count it one time (not one time for every time it occurred.)

    Example

    Date Hour
    1-1-01 1200
    1-1-01 1245
    1-4-01 0800
    1-4-01 0810
    1-4-01 0855


    For 1-1-01 two calls occurred during the noon hour - if I did a =countifs($a$2:$a$6,"="&$a2,$b$2:$b$6,"="&b2) the result would be 2 (for 1-4-01 the result would be 3!). I just need to know that it occurred at all - then I need to be able to add the number independently for all days of the week.

    This is a the begining of a staffing model - trying to determine the likelyhood of more than one call occuring during a particular hour on a particular day of the week.

    Help??

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: complex count formula?

    You can just add >0 to the end of your countifs formula.
    It will then return a True False if it occurred at least once.

    =countifs($a$2:$a$6,"="&$a2,$b$2:$b$6,"="&b2)>0

    FYI, you don't have to concatenate the A2 and B2 cell references with the = symbol.
    You only need to do that for > or < comparisons

    =countifs($a$2:$a$6,$a2,$b$2:$b$6,b2)>0

  3. #3
    Registered User
    Join Date
    07-01-2013
    Location
    South Dakota
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: complex count formula?

    Thanks for the hint on the = symbol!

    However, I'm not quite where I need to be... I need to take it one step further an count the number of Monday 0800 in a given month where the number of calls >2 in that hour interval. If it happened one monday in the month I need the answer to be 1. If it happened 3 Mondays in the month it should be 3. Even if there were dozens of calls that occurred in the 0800 hour on any or all Mondays of the month.

    I hope this is better explained.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1