Countif with 2 criteria

[AdamJones]

I need to make a formula which counts dates and times as one rather than trying to count them manually:

The details are:

Sheet1 - Column G contains the date (29/07/2017 or 30/07/2017) and Column H contains the time (formatted as h.mm AM/PM)

In a separate sheet I have a "chart" with dates at the top (29/07/2017 & 30/07/2017) and times down the side (in hourly increments) and I would like to be able to insert a formula which counts the time they are arriving only if they are arriving on x date etc.


Any help would be greatly appreciated

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F	G	H
1		29/07/2017	30/07/2017				date	time
2	12:00 AM	2	1				30/07/2017	08:30 PM
3	01:00 AM	0	0				30/07/2017	05:01 PM
4	02:00 AM	0	1				30/07/2017	06:22 AM
5	03:00 AM	0	0				30/07/2017	12:42 PM
6	04:00 AM	0	0				30/07/2017	12:24 AM
7	05:00 AM	0	0				29/07/2017	11:51 AM
8	06:00 AM	0	1				29/07/2017	12:45 AM
9	07:00 AM	0	0				30/07/2017	02:33 AM
10	08:00 AM	1	0				29/07/2017	08:36 AM
11	09:00 AM	0	0				29/07/2017	12:38 AM
12	10:00 AM	0	0
13	11:00 AM	1	0
14	12:00 PM	0	1
15	01:00 PM	0	0
16	02:00 PM	0	0
17	03:00 PM	0	0
18	04:00 PM	0	0
19	05:00 PM	0	1
20	06:00 PM	0	0
21	07:00 PM	0	0
22	08:00 PM	0	1
23	09:00 PM	0	0
24	10:00 PM	0	0
25	11:00 PM	0	0
26	12:00 AM	0	0
27
28
Sheet1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(($G$2:$G$11=B$1)*($H$2:$H$11>=$A2)*($H$2:$H$11<$A3))

 

[Scott T]

Countifs can have multiple criteria.

https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842