Results 1 to 5 of 5
  1. #1
    New Lounger mcwhiskers's Avatar
    Join Date
    Mar 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    CountIfs involving time calculations...

    Hi All,

    First of all - I've not used this site since the old WOPR days back in 19 hundred and 93!!!! Crikey - that sure makes me feel old. Lets hope its still as helpful as it was then.

    Anyway - example Excel 2010 attached. I've captured some call ticket information in a workbook. This analyses the amount of time certain teams have worked on the ticket - from when the user first logged the ticket to when the ticket was solved. Whole reason for this is that my company are looking to introduce revised SLAs - and I'd also like OLAs in place to augment this.

    Anyway - back to the workbook.

    As any good workbook should be - Im looking to make it as dynamic as possible for analysis/flexing purposes.

    So - in the Summary worksheet - in cell P14 - I have a CountIfs statement - but Im struggling to get it in the 2nd criteria to only select those cells in the range '120312 to 230312'!K4:K229 that are less than the number of hours in cell L14 (you can see I've manually entered the 02:00 to get by).

    Anyhelp here would be very much appreciated.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    This tests out on my machine.
    =COUNTIFS('120312 to 230312'!D4:D229,"=2",'120312 to 230312'!K4:K229,CONCATENATE("<",L14))
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    mcwhiskers (2012-04-02)

  4. #3
    New Lounger mcwhiskers's Avatar
    Join Date
    Mar 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hey RG - this works - many thanks here for this.

    Any ideas why the original formula doesn't work?

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    I'm glad it's working for you.

    What was your original formula? The one in the attached worksheet works but you've hard coded the test criteria in those.
    The only guess I can make from the information provided is that you didn't effectively create a single string value for the criteria. This is why I used the concatenate() function to create that single string criteria from the test "greater than" symbol and the value in L14.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    For cell [P14] the formula you needed was:
    =COUNTIFS('120312 to 230312'!D4:229,"=2",'120312 to 230312'!K4:K229,"<"&L14)

    For cell [Q14] the formula you needed was:
    =COUNTIFS('120312 to 230312'!D4:229,"=2",'120312 to 230312'!K4:K229,">="&L14)

    RG's concatenate formula also works.

    The attached file shows the fixes you were looking for.
    I didn't alter formulas in columns [R] and [S].

    zeddy
    Attached Files Attached Files

Posting Permissions

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