How to COUNTIF between two rows

Question

Hello,

Hopefully I can explain this to have someone find a fix for me :)

I want to could the amount of calls between each hour of the day for each type of call

For example the raw data looks like this
Hour

Call Time	Disconnect Reason
0:57:56	PRESENTED
8:19:14	ABANDONED
8:43:05	ABANDONED
8:44:22	CANCELLED
8:58:24	ABANDONED
9:27:13	ABANDONED
9:31:47	ABANDONED
9:33:58	ABANDONED
9:37:19	CANCELLED

And I want it to break it down like this *NOTE: I have worked out all calls using =COUNTIFS(range,">="&M7,range,"<="&N7) I just can't work out the other two as I would have to include the time AND the reasons ie Abandoned

Hour		All calls	Presnted+Cancelled	Abandoned
00:00:00	01:00:00	3
01:00:00	02:00:00	1
02:00:00	03:00:00	0
03:00:00	04:00:00	1
04:00:00	05:00:00	1
05:00:00	06:00:00	2
06:00:00	07:00:00	2
07:00:00	08:00:00	2
08:00:00	09:00:00	15
09:00:00	10:00:00	23
10:00:00	11:00:00	53
11:00:00	12:00:00	52
12:00:00	13:00:00	51
13:00:00	14:00:00	41
14:00:00	15:00:00	37
15:00:00	16:00:00	38
16:00:00	17:00:00	32
17:00:00	18:00:00	31
18:00:00	19:00:00	23
19:00:00	20:00:00	8
20:00:00	21:00:00	5
21:00:00	22:00:00	5
22:00:00	23:00:00	6
23:00:00	00:00:00	3

Thank you in advance :)

Answer 1

I am so sorry I am not following as the link did not provide me information on how you achieved the above result

Haylee,

Haven't you ever used a pivot table?

Is a sample file helpful? It contains only your data and a pivot table, no formulas, no macro.

https://www.dropbox.com/s/yca1z827jnq41pb/1bf47aad-61e7-4bac-88b0-7c42cc0dfa04.xlsx?dl=1

I've pasted your data into a new file

A1 is selected

Insert \ Pivot table placed into the same sheet D1

Drag&Drop the "Call Time" field to the rows section

Drag&Drop the "Disconnect Reason" field to the values section and columns section

Done!

Okay, I changed the layout of the Pivot table to Tabular format, so it looks a bit nicer. ;-)

Andreas.

Answer 2

All Calls

=COUNTIFS($A$2:$A$10,">"&D2,$A$2:$A$10,"<="&E2)

Abandoned 

=COUNTIFS($A$2:$A$10,">"&D2,$A$2:$A$10,"<="&E2,$B$2:$B$10,H$1)

Or

=COUNTIFS($A$2:$A$10,">"&D2,$A$2:$A$10,"<="&E2,$B$2:$B$10,"Abandoned")

Presnted + Cancelled 

=COUNTIFS($A$2:$A$10,">"&D2,$A$2:$A$10,"<="&E2,$B$2:$B$10,"Presented ")+COUNTIFS($A$2:$A$10,">"&D2,$A$2:$A$10,"<="&E2,$B$2:$B$10,"Cancelled ")

Or

=F2-H2

Answer 3

Hey!

I am so sorry I am not following as the link did not provide me information on how you achieved the above result

Thank you so much

Haylee

Answer 4

Simple Pivot table "Call Time" grouped by hours.

Create a PivotTable to analyze worksheet data - Office Support

See also "Group or ungroup data in a PivotTable", same article.

Andreas.