I have attached an example of the problem, the company database spits out the duration data into this format and I need to be able to get the count of calls that exceed 3min. If it was a proper time format it would be easy with a countifs formula but unfortunately it is not.
Any help would be appreciated.
Last edited by percyth1; 01-31-2012 at 08:29 PM.
Try this, in F2, copy down.
=SUMPRODUCT(COUNTIFS(A$1:A$11,E2,B$1:B$11,ROW(A$1:A$4)-1&"m*"))
This will count the minutes <=3.
If you are looking to count <=6 minutes change ROW(A$1:A$7)-1
ROW(A$1:A$lookup_minutes+1)-1
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
Put this formula in F2:
=SUMPRODUCT((--LEFT($B$2:$B$11,SEARCH("m",$B$2:$B$11)-1)>=3)*($A$2:$A$11=E2))
then copy down for as many employees as you have in column E.
Hope this helps.
Pete
Thanks, but can this work if the calls go above 10min?
Yes, See the attached.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
I'm meant to say count of calls over 3min but that still works. I can deduct it off the total.
Thanks.
Last edited by percyth1; 01-31-2012 at 10:15 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks