Excel formula to calculate call center backlog

Hello Excel Gurus,

I have a list of support calls over a 17 weeks time span and I am trying to calculate the backlog for each week. My data range contains the created date and the closed date (some closed dates are empty since the calls are still open) I have tried a few ways with no luck (pivot table, countif, sumif) the problem I am running into is that for each week I have to check and add the backlog from the previous week (only those calls that carry over, so are still open)

Any help is greatly appreciated!

Please Login or Register  to view this content.

Can you post a file with the data and the expected result?

In cell E2 enter the following formula:
=COUNTIFS($C$2:$C$1034,$C2,$D$2:$D$1034,">" &0)

Originally Posted by Glenn Kennedy

Try this, =COUNTIF($D$2:D2,0)

but set your calculation options to automatic, as well!!.

It returns all 0s. Calculation option is set to 'Automatic'

Originally Posted by Glenn Kennedy

??? Are you sure. See attached sheet.

First week's backlog should be 5. There are 5 calls created on the first week and none of them were resolved (closed) till the second week. What does the '0' in the criteria mean in your formula?

Thank you for your help.

Originally Posted by Glenn Kennedy

Exactly what do you want to count?

I have given you the number of open calls, increasing cumulatively. That may have been incorrect.

So do you want the result to be any calls that were closed later than the week in which they were created PLUS open calls from previous weeks? If that is so, you can NEVER reduce the back log, as a late closure will ALWAYS count as a late closure.

So ca you take a moment to explain EXACTLY what you want a sum of?

What I am looking for is the number of calls open for the week. So the count of all calls whose closed date is later than the current week. Hope this clarifies my scope.

Thank you again for your help.

here is what I have so far.
I know that at the end of the first week I should have a backlog of 5 and at the end of the last week the backlog should be somewhere close to 365. (these are my control values.)

Please Login or Register  to view this content.

Originally Posted by bebo021999

Can you manual give some desired results in E column?

Hi bebo021999,

The example i provided in comment #3 is as close as I can come up with, in addition to that week 17 should be 365. The other weeks I am trying to calculate.

Originally Posted by bebo021999

If I interpret correctly, this is the logic:

For example: .2nd week of 2015 ending on Sartuday, 10-Jan. Backlog count of week 2 for all rows those open earlier 10-Jan and close later than 10-Jan?

20-Jan --- 02-Jan : count 1
09-Jan --- 08-Jan : count 0
blank -----7-Jan : count 1

Is that true?

You are correct. I would like to carry over that logic for every week.

Thank you.

Should the backlog for week 2 be 40? I calculated this by taking the backlog count for week 2 itself which totals 39 and then adding 1 from row 6 where that item went 4 weeks which means it should be counted in week 2.

Originally Posted by NS4Excel

Should the backlog for week 2 be 40? I calculated this by taking the backlog count for week 2 itself which totals 39 and then adding 1 from row 6 where that item went 4 weeks which means it should be counted in week 2.

That would be correct!

Originally Posted by bebo021999

Sorry I think my formula in post #19 does not correct, please update with this:

Please Login or Register  to view this content.

Confirmed with Ctrl-shift-enter

Thank you bebo021999, I think this might have solved my problem. Aprreciate everyone's help on this.

Hello Guys,
I need to compute help desk backlog.
I don't retreive 40 for week 2 as said in comment #17 and #18 but 34.
Could you please help me to find what's wrong in my file?
Sorry it is with a french excel.