# Thread: CountIfs involving time calculations...

1. ## 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.

2. This tests out on my machine.
=COUNTIFS('120312 to 230312'!D4:D229,"=2",'120312 to 230312'!K4:K229,CONCATENATE("<",L14))

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

mcwhiskers (2012-04-02)

4. Hey RG - this works - many thanks here for this.

Any ideas why the original formula doesn't work?

5. 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.

6. 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

#### Posting Permissions

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