Countifs function for Date & Time Range not counting all cells

[SMcCafferty]

I am using the formula below to count all cells that fall between two specified date/time ranges. From what I can tell, it is counting the cells between the two date/times but not the boundary cells which meet the criteria.

The function is counting cells on a separate worksheet, but is contained within the same workbook. The References AL2 and AL3 are helper cells I created to combine the Date and Time entered on the sheet specifying the date/time range needed.

=COUNTIFS('KPI Table'!AM3:AM491,">"&AL2,'KPI Table'!AM3:AM491,"<"&AL3)

I am also using SUMIFS formulas in the adjacent cells and getting the same result.

Any help would be greatly appreciated.

PS: Below is the code from the two sheets I am using captured by Xl2BB. Not sure if that is what you want. I dont see an option where I can upload the actual Excel file.

Countifs Example.xlsx
	A	B	C	D
8	Total Frac Stages		4
9	Total Pumping Hours		10:56
10	Total NPT Hours		5:42
Daily Ops Report

Cell Formulas
Range	Formula
C8	C8	=COUNTIFS('KPI Table'!AM3:AM491,">"&AL2,'KPI Table'!AM3:AM491,"<"&AL3)
C9	C9	=SUMIFS('KPI Table'!AN3:AN491,'KPI Table'!AM3:AM491,"<="&AL3,'KPI Table'!AM3:AM491,">="&AL2)
C10	C10	=SUMIFS('KPI Table'!AO3:AO491,'KPI Table'!AM3:AM491,">="&AL2,'KPI Table'!AM3:AM491,"<="&AL3)

Countifs Example.xlsx
	AK	AL	AM	AN	AO
2	Date	Pump End Time	Date-Time Helper	Pump Hours	NPT Hours
3	4/7/2022	0:15	4/7/22 12:15 AM	2:09	0:00
4	4/7/2022	5:53	4/7/22 5:53 AM	2:38	2:30
5	4/7/2022	8:25	4/7/22 8:25 AM	2:12	3:05
6	4/7/2022	13:33	4/7/22 1:33 PM	3:30	1:08
7	4/7/2022	16:21	4/7/22 4:21 PM	2:05	0:13
8	4/7/2022	22:21	4/7/22 10:21 PM	3:09	1:16
9	4/7/2022	2:28	4/7/22 2:28 AM	2:20	1:26
10	4/7/2022	5:54	4/7/22 5:54 AM	2:17	0:39
11	4/8/2022	9:15	4/8/22 9:15 AM	2:36	0:15
12	4/8/2022	15:40	4/8/22 3:40 PM	2:40	3:15
13	4/9/2022	6:35	4/9/22 6:35 AM	2:19	12:06
KPI Table

Cell Formulas
Range	Formula
AK3:AK13	AK3	=IF('C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!A6="","",'C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!A6)
AL3:AL13	AL3	=IF('C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!L6="","",'C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!L6)
AM3:AM13	AM3	=AK3+AL3
AN3:AN13	AN3	=IF('C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!M6="","",'C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!M6)
AO3:AO13	AO3	=IFERROR('C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!J6+'C:\Users\spmcc\Documents\4. Job Management Packet\[JMP 6.06 Vlookup.xlsm]Frac Stage Tracker'!P6,"")

 

[Alex Blakenburg]

Your 2nd XL2BB is full of exgternal references to errors out at my end.
I am not entirely clear on what you are trying to do.
I am also struggling with this statement and then using < & > in the formula and not =< & >=.

From what I can tell, it is counting the cells between the two date/times but not the boundary cells which meet the criteria.

So a bit of a shot in the dark, does this work.

=COUNTIFS('KPI Table'!AM3:AM491,">"&ROUND(AL2-0.00001,5),'KPI Table'!AM3:AM491,"<"&ROUND(AL2+0.00001,5))

 

[SMcCafferty]

Your 2nd XL2BB is full of exgternal references to errors out at my end.
I am not entirely clear on what you are trying to do.
I am also struggling with this statement and then using < & > in the formula and not =< & >=.

So a bit of a shot in the dark, does this work.

=COUNTIFS('KPI Table'!AM3:AM491,">"&ROUND(AL2-0.00001,5),'KPI Table'!AM3:AM491,"<"&ROUND(AL2+0.00001,5))

First and foremost, I apologize for the mess I sent the first time. It was my first time using XL2BB and it appears I made a pig's ear of it. The short answer to your formula is that no, it did not work. But, I realize I may not have been clear enough in my first attempt at explaining so please allow me to clarify;
1. The original formula was "<=" and also ">=" I accidentally sent my last attempt at trying some permutations to resolve the issue.
2. I hope the examples below will help better. But essentially, I am trying to count cells that are within a 24Hr period. The first mini-sheet is taken from the report where I am displaying the data. For the purposes of this I moved the helper column which combines the date and time entries into one cell to cells E2 and E3. This will take the place of AL2 and AL3. The location of the cells I am not concerned with, I can adjust the formula once I know what I am doing wrong.
3. The cells that I am trying to count are in a table contained on a separate worksheet (KPI Table), but, both worksheets are in the same workbook. That is the second mini-sheet in this reply.

Thank you for replying so quickly and helping me on this.

JMP 6.06 Vlookup.xlsm
	A	B	C	D	E
1	From:	4/7/2022	6:00 AM		4/7/22 6:00
2	To:	4/8/2022	6:00 AM		4/8/22 6:00
3
4	Pad Name/Well Numbers			PLU 18 TWR : 152H 162H
5
6	Operations Stats
7	Total Frac Stages		0
8	Total Pumping Hours		10:56
9	Total NPT Hours		5:42
Daily Ops Report

Cell Formulas
Range	Formula
E1:E2	E1	=B1+C1
D4	D4	=CONCATENATE('Well Data Input'!C1&": "&'Well Data Input'!B12&" "&'Well Data Input'!D12&" "&'Well Data Input'!F12&" "&'Well Data Input'!H12&" "&'Well Data Input'!J12&" "&'Well Data Input'!L12&" "&'Well Data Input'!N12&" "&'Well Data Input'!P12)
C7	C7	=COUNTIFS('KPI Table'!B2:B491,">="&ROUND(E2-0.00001,5),'KPI Table'!B2:B491,"<="&ROUND(E3+0.00001,5))
C8	C8	=SUMIFS('KPI Table'!$G$2:$G$491,'KPI Table'!$B$2:$B$491,"<="&$AL$3,'KPI Table'!$B$2:$B$491,">="&$AL$2)
C9	C9	=SUMIFS('KPI Table'!$AF$2:$AF$491,'KPI Table'!$B$2:$B$491,"<="&$AL$3,'KPI Table'!$B$2:$B$491,">="&$AL$2)

Named Ranges
Name	Refers To	Cells
'KPI Table'!_FilterDatabase	='KPI Table'!$B$2:$B$491	C7:C9
'KPI Table'!Criteria	='KPI Table'!$B$2:$B$491	C7:C9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B2	Cell	contains a blank value	text	NO

JMP 6.06 Vlookup.xlsm
	B	C	D	E	F	G	H
1	Date	Well Name	Stage	Transition Time (Hrs)	NPT (Hrs)	Pump Time (Hrs)	Add. NPT (Hrs)
2	4/7/22 0:15	152H	1	0:16		2:09
3	4/7/22 5:53	162H	1	0:30	2:30	2:38
4	4/7/22 8:25	152H	2	0:30	3:05	2:12
5	4/7/22 13:33	162H	2	0:30	1:08	3:30
6	4/7/22 16:21	152H	3	0:30	0:13	2:05
7	4/7/22 22:21	162H	3	0:30	1:16	3:09
8	4/7/22 2:28	152H	4	0:30	1:26	2:20
9	4/7/22 5:54	162H	4	0:30	0:39	2:17
10	4/8/22 9:15	152H	5	0:30	0:15	2:36
11	4/8/22 15:40	162H	5	0:30	3:15	2:40
12	4/9/22 6:35	152H	6	0:30	12:06	2:19
13	4/9/22 10:09	162H	6	0:30	0:39	2:25
14	4/9/22 13:29	152H	7	0:30	0:31	2:19
15	4/9/22 17:03	162H	7	0:30	0:17	2:47
16	4/9/22 22:08	152H	8	0:30	0:30	3:19	0:46
17	4/9/22 1:19	162H	8	0:30	5:05	2:41
18	4/9/22 4:47	152H	9	0:30	0:27	2:31
19	4/10/22 8:00	162H	9	0:38		2:35
20	4/10/22 14:32	152H	10	0:30	3:49	2:13
21	4/10/22 17:07	162H	10	0:08		2:27
22	4/10/22 21:11	152H	11	0:30	0:37	2:57
23	4/11/22 0:43	162H	11	0:30	0:42	2:20
24	4/11/22 4:59	152H	12	0:30	1:38	2:08
25	4/11/22 8:08	162H	12	0:30	0:08	2:31
26	4/11/22 10:41	152H	13	0:23		2:10
27	4/11/22 14:13	162H	13	0:30	0:05	2:57
28	4/11/22 16:45	152H	14	0:21		2:11
29	4/11/22 19:31	162H	14	0:28		2:18
30	4/11/22 1:27	152H	15	0:30	3:03	2:23
31	4/11/22 6:13	162H	15	0:30	1:16	3:00
32	4/12/22 9:58	152H	16	0:30	0:38	2:37
33	4/12/22 13:41	162H	16	0:30	0:33	2:40
34	4/12/22 16:23	152H	17	0:30	0:07	2:05
KPI Table

Cell Formulas
Range	Formula
B2:B34	B2	=IF('Frac Stage Tracker'!$A6+'Frac Stage Tracker'!$L6="","",'Frac Stage Tracker'!$A6+'Frac Stage Tracker'!$L6)
C2:C34	C2	=IF('Frac Stage Tracker'!B6="","",'Frac Stage Tracker'!B6)
D2:D34	D2	=IF('Frac Stage Tracker'!D6="","",'Frac Stage Tracker'!D6)
E2:E34	E2	=IF('Frac Stage Tracker'!G6="","",'Frac Stage Tracker'!G6)
F2:F34	F2	=IF('Frac Stage Tracker'!J6=0,"",'Frac Stage Tracker'!J6)
G2:G34	G2	=IF('Frac Stage Tracker'!M6=0,"",'Frac Stage Tracker'!M6)
H2:H34	H2	=IF('Frac Stage Tracker'!P6=0,"",'Frac Stage Tracker'!P6)

 

[Alex Blakenburg]

Can you copy KPI Table as values only to another sheet and then post an XL2BB of that here.
(I don't have Frac Stage Tracker so I finish up with no data in KPI Table)
Also your sample results rely on AL2 & AL3, but it is not showing in your Daily Ops report. What values do you have in AL2 & 3 that are used be C7-C9.

If you need to you can share the workbook via Google Drive, One Drive, Dropbox etc. Just allow anyone with the link to access it and post the link here.

 

[SMcCafferty]

Can you copy KPI Table as values only to another sheet and then post an XL2BB of that here.
(I don't have Frac Stage Tracker so I finish up with no data in KPI Table)
Also your sample results rely on AL2 & AL3, but it is not showing in your Daily Ops report. What values do you have in AL2 & 3 that are used be C7-C9.

If you need to you can share the workbook via Google Drive, One Drive, Dropbox etc. Just allow anyone with the link to access it and post the link here.

Alex,
For the purposes of this I moved AL2 and AL3 to cells E2 and E3 in the first mini sheet on the last post. I figured it would be easier. The actual report is quite large and as I don't want AL2 and AL3 visible to the user it exists outside of the printable area in hidden cells. Below is a sample from the KPI table in values only. I apologize for all of this. The workbook is quite large and loaded with VBA, Macros and formulas.

KPI Table-Values Only.xlsx
	A	B	C	D	E	F	G
1	Date	Well Name	Stage	Transition Time (Hrs)	NPT (Hrs)	Pump Time (Hrs)	Add. NPT (Hrs)
2	4/7/22 0:15	152H	1	0:16		2:09
3	4/7/22 5:53	162H	1	0:30	2:30	2:38
4	4/7/22 8:25	152H	2	0:30	3:05	2:12
5	4/7/22 13:33	162H	2	0:30	1:08	3:30
6	4/7/22 16:21	152H	3	0:30	0:13	2:05
7	4/7/22 22:21	162H	3	0:30	1:16	3:09
8	4/7/22 2:28	152H	4	0:30	1:26	2:20
9	4/7/22 5:54	162H	4	0:30	0:39	2:17
10	4/8/22 9:15	152H	5	0:30	0:15	2:36
11	4/8/22 15:40	162H	5	0:30	3:15	2:40
12	4/9/22 6:35	152H	6	0:30	12:06	2:19
13	4/9/22 10:09	162H	6	0:30	0:39	2:25
14	4/9/22 13:29	152H	7	0:30	0:31	2:19
15	4/9/22 17:03	162H	7	0:30	0:17	2:47
16	4/9/22 22:08	152H	8	0:30	0:30	3:19	0:46
17	4/9/22 1:19	162H	8	0:30	5:05	2:41
18	4/9/22 4:47	152H	9	0:30	0:27	2:31
19	4/10/22 8:00	162H	9	0:38		2:35
20	4/10/22 14:32	152H	10	0:30	3:49	2:13
21	4/10/22 17:07	162H	10	0:08		2:27
22	4/10/22 21:11	152H	11	0:30	0:37	2:57
23	4/11/22 0:43	162H	11	0:30	0:42	2:20
24	4/11/22 4:59	152H	12	0:30	1:38	2:08
25	4/11/22 8:08	162H	12	0:30	0:08	2:31
26	4/11/22 10:41	152H	13	0:23		2:10
27	4/11/22 14:13	162H	13	0:30	0:05	2:57
28	4/11/22 16:45	152H	14	0:21		2:11
29	4/11/22 19:31	162H	14	0:28		2:18
30	4/11/22 1:27	152H	15	0:30	3:03	2:23
31	4/11/22 6:13	162H	15	0:30	1:16	3:00
32	4/12/22 9:58	152H	16	0:30	0:38	2:37
33	4/12/22 13:41	162H	16	0:30	0:33	2:40
34	4/12/22 16:23	152H	17	0:30	0:07	2:05
35	4/12/22 19:23	162H	17	0:29		2:31
Sheet1

 

[Alex Blakenburg]

Your pumping SUMIFS formula seems to be working fine. Can you give me an example of it not working.
eg the AL values for
Start Date & Time
End Date & Time
Pumping Value expected result
actual result

 

[SMcCafferty]

Your pumping SUMIFS formula seems to be working fine. Can you give me an example of it not working.
eg the AL values for
Start Date & Time
End Date & Time
Pumping Value expected result
actual result

Alex,

On the CountIf (Total Stages) the Ops Report shows a result of 4. But the value should be 6 as there were 6 stages completed between 4/7 6AM and 4/8 6AM. The pumping and NPT hours are also only counting 4 stages worth of time.

 

[Alex Blakenburg]

On the CountIf (Total Stages) the Ops Report shows a result of 4. But the value should be 6 as there were 6 stages completed between 4/7 6AM and 4/8 6AM. The pumping and NPT hours are also only counting 4 stages worth of time.

Unless I am issing something the result of 4 is correct.
Your start date & time it FROM 6 AM on 4 Jul 2022.

2 of the times you are counting in your check total of 6 are before 6 AM.

 

[SMcCafferty]

Unless I am issing something the result of 4 is correct.
Your start date & time it FROM 6 AM on 4 Jul 2022.

2 of the times you are counting in your check total of 6 are before 6 AM.


View attachment 64071

Yes sir, I need to count those. The time span is from 6am to 6am. So, I need it to count all cells that begin after 6AM on 4/7 and before 6AM on 4/8/

 

[Alex Blakenburg]

But they are before 6 AM on 4/7.