COUNT Formula Help

[KuraiChikara]

Hello,
I need to find out how many calls come in, for any given day in a month from 7a-7p,.
Criteria is in hour increments 7a-8a, 8a-9a, 9a-10a, 10a-11a, 11a-12p, 12p-1p, 1p-2p, 2p-3p, 3p-4p, 4p-5p, 5p-6p, 6p-7p (12 possible groupings).

Example: if I had calls from 07:01, 07:59, 08:00 and 08:59 all come in 03/26/2019 that would be counted as 4 calls for that day between the range of 7a-8a.

Data:
A2 would contain the date format IE 03/01/2019, 03/02/2019 ' ' ' ' ' ' '03/26/2019, etc
B2 would contain random times from 07:00 to 19:00 IE 07:59, 10:13, 19:59, etc
C3 would have the formula to give me a counted result with the 12 possible grouping criteria that I mentioned which I don't know how to do

I don't know what formula would make this easiest to accomplish, I was thinking =LOOKUP but I'm not sure that will help me. I know I could do a possible nested count if or count ifs but that'll make my sheet large. I'm hopefully looking for a simpler method.

 

[RasGhul]

Hi Kurai,

Can you use this?

Book1
	A	B	C
1	Date	Times	Timestamp
2	25-03-19	7:01	25-03-19 7:01
3	25-03-19	9:30	25-03-19 9:30
4	25-03-19	13:00	25-03-19 13:00
5	25-03-19	17:30	25-03-19 17:30
6	26-03-19	8:36	26-03-19 8:36
7	26-03-19	9:10	26-03-19 9:10
8	26-03-19	11:11	26-03-19 11:11
9	26-03-19	12:45	26-03-19 12:45
10	26-03-19	7:50	26-03-19 7:50
11	26-03-19	7:01	26-03-19 7:01
12	26-03-19	9:30	26-03-19 9:30
13	27-03-19	13:00	27-03-19 13:00
14	27-03-19	17:30	27-03-19 17:30
15	27-03-19	8:36	27-03-19 8:36
16	27-03-19	9:10	27-03-19 9:10
17	27-03-19	11:11	27-03-19 11:11
18	27-03-19	12:45	27-03-19 12:45
19	27-03-19	7:50	27-03-19 7:50
20	27-03-19	8:50	27-03-19 8:50
Sheet1

Cell Formulas
Range	Formula
C2	=A2+B2

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	26-03-19
2	Day Count	7
3
4	7:00 AM	8:00 AM	9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM	6:00 PM
5	8:00 AM	9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM	6:00 PM	7:00 PM
6	2	1	2		1	1
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIFS(Sheet1!$A$2:$A$20,">="&Sheet2!$B$1,Sheet1!$A$2:$A$20,"<"&(Sheet2!$B$1+1))
A6	=COUNTIFS(Sheet1!$C$2:$C$20,">="&(Sheet2!$B$1+Sheet2!A$4),Sheet1!$C$2:$C$20,"<"&(Sheet2!$B$1+Sheet2!A$5))

 

[etaf]

use a countifs for each hour range

=COUNTIFS(B2:B1000," > = "&TIMEVALUE("7:00"),B2:B1000," < = "&TIMEVALUE("19:00"),A2:A1000,DATEVALUE("27/3/18"))
where B2:B1000 is the range of times
A2:A1000 is the dates
Change these to suite your data range

Date I have hardcoded but you could change that to reference a particular cell - so the count changes based on the date in that cell

groups
Is this a typo
Example: if I had calls from 07:01, 07:59, 08:00 and 08:59 all come in 03/26/2019 that would be counted as 4 calls for that day between the range of 7a-8a.

8:59 would be a different hour group
If you want each hour group
then

=COUNTIFS(B2:B1000," > ="&TIMEVALUE("7:00"),B2:B1000," < "&TIMEVALUE("8:00"),A2:A1000,DATEVALUE("27/3/18"))
=COUNTIFS(B2:B1000," > ="&TIMEVALUE("8:00"),B2:B1000," < "&TIMEVALUE("9:00"),A2:A1000,DATEVALUE("27/3/18"))

repeat for each hour
NOT this will NOT include 19:00 . as i just have less than
so the last group
=COUNTIFS(B2:B1000," > ="&TIMEVALUE("18:00"),B2:B1000," < ="&TIMEVALUE("19:00"),A2:A1000,DATEVALUE("27/3/18"))
changes to < =