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 09: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
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
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.
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 11:15 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks