Countif Command With 18 Time Ranges

  • Thread starter Thread starter niyazb
  • Start date Start date
N

niyazb

Hello all,
I have a worksheet with contains minimum of 700 records basically daily
call management sheet I need to count number of calls which been
attended in specific time range

Example as Call register at February 16, 2004 at 15:41 and the same
call has been attended and closed at February 16, 2004 at 16:41 I minus
closing time from attending time and result is 00 day(s) 01:00 which
means that the specific call has took one hour to be completed. I need
to count how many calls have been in the following time range:

Calls attended within 5 mins 15, 30, 60, 2 hour, 3 hours, 1 day, 2 day
+ unattended calls and so on

I tried countif formula and tried to use if condition but with our
success.

Please assist I need to submit my monthly report.

:confused: :eek: :mad: :rolleyes: :( :)
 
Hi
try the following:
- create a new column (lets say col H) which stores the completion time
- Now you may use SUMPRODUCT to evaluate your calls. e.g.
=SUMPRODUCT((H1:H100>=TIME(0,5,0))*(H1:H100<=TIME(0,15,0))

Another idea would be to add an additional helper column which
evaluates the respective class for each call (e.g. with IF clauses) and
using a pivot table on this data
 
Assuming the data in column A, in D1 add

=COUNTIF(A:A,"<"&5/60/24)

D2: =COUNTIF(A:A,"<="&15/60/24)-SUM($D$1:D1)

D3: =COUNTIF(A:A,"<="&30/60/24)-SUM($D$1:D2)
etc.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi niyazb!

I think we need more details here. The counting for times is easy
enough but you are mixing in others. How are you recording
"unattended" and what are the "and so on"?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Niayzb,

But solution worked fine. As you have the ranges in cells I changed it as
follows

P3: =COUNTIF(K:K,"<"&O3/60/24)
P4: =COUNTIF(K:K,"<"&O4/60/24)-SUM($P3:P)
P4 copied down to P19
P20: =COUNTA(K:K)-SUM($P3:P9)

gave results of

5 Minutes 5
15 Minutes 8
30 Minutes 5
60 Minutes 4
120 Minutes 4
180 Minutes 3
240 Minutes 3
300 Minutes 1
360 Minutes 2
420 Minutes 3
480 Minutes 2
1440 Minutes 64
2880 Minutes 12
4320 Minutes 6
5760 Minutes 3
7200 Minutes 4
8640 Minutes 2
unattended 45


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"niyazb >" <<niyazb.11q62b@excelfor
um-nospam.com> wrote in message news:[email protected]...
 
O hi Bob Phillips thank you very much for your effort the formula i
working however its not generating correct results

This is what am getting using the formula
Time Range Output
5 Minutes 6
15 Minutes 7
30 Minutes 11
60 Minutes 11
120 Minutes 15
180 Minutes 14
240 Minutes 18
300 Minutes 15
360 Minutes 20
420 Minutes 18
480 Minutes 22
1440 Minutes 82
2880 Minutes 34
4320 Minutes 88
5760 Minutes 37
7200 Minutes 92
8640 Minutes 39
unattended
Total 529

If you see the total calls is showing 529 calls and in the attache
file the calls are only 176. even when check the results of calls wit
been attended within 5 mins (from Zero to 5 mins) it showing 6 calls i
the actual file they suppose to be 25 calls


By the way I have changed the from K:K to N:N & added cell number t
end of the given formula =COUNTIF(N:N,"<"&O4/60/24)-SUM($P3:P3)
Because it was showing “#NAME?” when I paste it.

GETTING CLOSER TO THE SOLUTIO
 
Special thanks to everyone how supported & helped me. At last I figure
it out the formula is working perfectly.

Thanks again
:) ;) :cool
 
Back
Top