Help extracting information to analysis

[laurapz]

[FONT=&quot] Good evening everyone

I am hoping someone on here maybe able to help me, I have search the net and I am sure there is a possible solution, I am just not sure I know how to find it. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I use a call log at work which comes to me showing the date of the incoming calls, time of calls (24 hours), if the call was accepted, missed or rejected.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Is it possible to produce results showing:[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Total number of calls accepted per hour. Calls missed per hour. Calls rejected per hour. Operator on duties name? Total number of calls for the day?[/FONT]
[FONT=&quot] The other information on the sheet is not need to be Incorporated into the results[/FONT]
[FONT=&quot]Thank you in advance, at present I am spending hours trailing over the information[/FONT]
[FONT=&quot] Example below of what I receive is below [/FONT]
[FONT=&quot] Thanking anyone who can help me save sometime or point me in the right in advance.[/FONT]

Name Date Time Action Action result Result description Duration Sun 05/03/2017 23:35 Phone call Accepted The call was connected to and was accepted by this number. 00:00:23 Sun 05/03/2017 23:24 Phone call Accepted The call was connected to and was accepted by this number. 00:00:32 Sun 05/03/2017 23:12 Phone call Accepted The call was connected to and was accepted by this number. 00:00:32 Sun 05/03/2017 23:08 Phone call Accepted The call was connected to and was accepted by this number. 00:00:58 Sun 05/03/2017 23:05 Phone call Missed 00:00:11 Sun 05/03/2017 23:05 Phone call Accepted The call was connected to and was accepted by this number. 00:01:08 Sun 05/03/2017 22:49 Phone call Accepted The call was connected to and was accepted by this number. 00:00:44 Sun 05/03/2017 22:44 Phone call Accepted The call was connected to and was accepted by this number. 00:00:33 Sun 05/03/2017 22:38 Phone call Accepted The call was connected to and was accepted by this number. 00:00:39 Sun 05/03/2017 22:37 Phone call Accepted The call was connected to and was accepted by this number. 00:00:29 Sun 05/03/2017 22:34 Phone call Accepted The call was connected to and was accepted by this number. 00:00:40 Sun 05/03/2017 22:34 Phone call Accepted The call was connected to and was accepted by this number. 00:00:40 Sun 05/03/2017 22:32 Phone call Accepted The call was connected to and was accepted by this number. 00:01:00 Sun 05/03/2017 22:08 Phone call Accepted The call was connected to and was accepted by this number. 00:00:41 Sun 05/03/2017 22:08 Phone call Accepted The call was connected to and was accepted by this number. 00:00:34 Sun 05/03/2017 22:07 Phone call Accepted The call was connected to and was accepted by this number. 00:00:22 Sun 05/03/2017 22:01 Phone call Accepted The call was connected to and was accepted by this number. 00:00:45 Sun 05/03/2017 21:58 Phone call Accepted The call was connected to and was accepted by this number. 00:00:29 <colgroup><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col width="138" style="width: 104pt;"><col width="101" style="width: 76pt;"><col width="91" style="width: 68pt;"></colgroup><tbody style="margin: 0px; padding: 0px; border: 0px; font-weight: inherit; vertical-align: baseline;">
</tbody>

 

[FDibbins]

With your data like this...

	A​	B​	C​	D​	E​	F​	G​
1​	Name	Date	Time	Action	Action result	Result description	Duration
2​		Sun 05/03/2017	23:35​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:23​
3​		Sun 05/03/2017	23:24​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:32​
4​		Sun 05/03/2017	23:12​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:32​
5​		Sun 05/03/2017	23:08​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:58​
6​		Sun 05/03/2017	23:05​	Phone call	Missed		0:00:11​
7​		Sun 05/03/2017	23:05​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:01:08​
8​		Sun 05/03/2017	22:49​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:44​
9​		Sun 05/03/2017	22:44​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:33​
10​		Sun 05/03/2017	22:38​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:39​
11​		Sun 05/03/2017	22:37​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:29​
12​		Sun 05/03/2017	22:34​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:40​
13​		Sun 05/03/2017	22:34​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:40​
14​		Sun 05/03/2017	22:32​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:01:00​
15​		Sun 05/03/2017	22:08​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:41​
16​		Sun 05/03/2017	22:08​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:34​
17​		Sun 05/03/2017	22:07​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:22​
18​		Sun 05/03/2017	22:01​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:45​
19​		Sun 05/03/2017	21:58​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:29​

Try this...

	I​	J​	K​	L​
1​		Accepted	Missed	Rejected
2​	20:00​	0​	0​	0​
3​	21:00​	1​	0​	0​
4​	22:00​	11​	0​	0​
5​	23:00​	5​	1​	0​

J2=COUNTIFS($E$2:$E$19,J$1,$C$2:$C$19,">="&$I2,$C$2:$C$19,"<"&$I2+1/24)
copied down and across as needed

 

[laurapz]

Thank you for the answer.

One question. The formula works perfectly in the first row but when I copy down I'm getting a value. Any ideas?

	A​	B​	C​	D​	E​	F​	G​
1​	Name	Date	Time	Action	Action result	Result description	Duration
2​		Sun 05/03/2017	23:35​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:23​
3​		Sun 05/03/2017	23:24​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:32​
4​		Sun 05/03/2017	23:12​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:32​
5​		Sun 05/03/2017	23:08​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:58​
6​		Sun 05/03/2017	23:05​	Phone call	Missed		0:00:11​
7​		Sun 05/03/2017	23:05​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:01:08​
8​		Sun 05/03/2017	22:49​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:44​
9​		Sun 05/03/2017	22:44​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:33​
10​		Sun 05/03/2017	22:38​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:39​
11​		Sun 05/03/2017	22:37​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:29​
12​		Sun 05/03/2017	22:34​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:40​
13​		Sun 05/03/2017	22:34​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:40​
14​		Sun 05/03/2017	22:32​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:01:00​
15​		Sun 05/03/2017	22:08​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:41​
16​		Sun 05/03/2017	22:08​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:34​
17​		Sun 05/03/2017	22:07​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:22​
18​		Sun 05/03/2017	22:01​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:45​
19​		Sun 05/03/2017	21:58​	Phone call	Accepted	The call was connected to and was accepted by this number.	0:00:29​

<tbody>
</tbody>

Try this...

	I​	J​	K​	L​
1​		Accepted	Missed	Rejected
2​	20:00​	0​	0​	0​
3​	21:00​	1​	0​	0​
4​	22:00​	11​	0​	0​
5​	23:00​	5​	1​	0​

<tbody>
</tbody>

J2=COUNTIFS($E$2:$E$19,J$1,$C$2:$C$19,">="&$I2,$C$2:$C$19,"<"&$I2+1/24)
copied down and across as needed[/QUOTE]

 

[FDibbins]

Sorry for the delay in replying, I was offline.

Can you show me the formula you used, and tell me which columns/rows your data is in?

 

[laurapz]

Sorry for the delay in replying, I was offline.

Can you show me the formula you used, and tell me which columns/rows your data is in?

=COUNTIFS($E$2:$E$19,J$1,$C$2:$C$19,">="&$I2,$C$2:$C19,"<"&$I2+1/24)

Using the above formal and below is how it is lay out, in columns, I, J, K and L. Would it make any difference that this is a csv file when sent to me?

	Accepted	Missed	Rejected
20:00	0
21:00	#VALUE!
22:00	#VALUE!
23:00	#VALUE!

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>