How do I sum the guillemot counts for each hour?

[silvaleroy]

I'm trying to get the sum of the guillemot data for each hour rather than each half hour, but the formula I got (from this site) doesn't allow me to discriminate between days. As you can see from my datasheet, some times there isn't a full hour and it moves on to the next day. So I'm wondering if there's an efficient way to sum data into hourly counts while discriminating between days. There's almost 3000 rows in this datasheet so I would prefer not to sum row by row manually if possible. Any help would be much appreciated. Thanks in advance!

SOS_MMS_2021_Camera1-D1_FromTW.csv
	A	B	C	D	E	F	G	H	I	J
1		DateTime	Year	Month	Day	Time	Guillemot	GuChick	DateTime	1HrMerged
2	1	202103000000	2021	3	22	10:29	0			0
3	2	202103000000	2021	3	22	10:32	0			0
4	3	202103000000	2021	3	22	11:02	0			0
5	4	202103000000	2021	3	22	11:32	0			0
6	5	202103000000	2021	3	22	12:02	0			0
7	6	202103000000	2021	3	22	12:32	0			0
8	7	202103000000	2021	3	22	13:02	0			0
9	8	202103000000	2021	3	22	13:32	0			0
10	9	202103000000	2021	3	22	14:02	0			0
11	10	202103000000	2021	3	22	14:32	0			0
12	11	202103000000	2021	3	22	15:02	0			0
13	12	202103000000	2021	3	22	15:32	0			0
14	13	202103000000	2021	3	22	16:02	0			0
15	14	202103000000	2021	3	22	16:32	0			0
16	15	202103000000	2021	3	22	17:02	0			0
17	16	202103000000	2021	3	22	17:32	0			0
18	17	202103000000	2021	3	22	18:02	0			0
19	18	202103000000	2021	3	23	05:45	0			0
20	19	202103000000	2021	3	23	06:15	0			0
21	20	202103000000	2021	3	23	06:45	0			0
22	21	202103000000	2021	3	23	07:15	0			0
23	22	202103000000	2021	3	23	07:45	0			0
24	23	202103000000	2021	3	23	08:15	0			0
25	24	202103000000	2021	3	23	08:45	0			0
26	25	202103000000	2021	3	23	09:15	0			0
27	26	202103000000	2021	3	23	09:45	0			0
28	27	202103000000	2021	3	23	10:15	0			0
29	28	202103000000	2021	3	23	10:45	0			0
30	29	202103000000	2021	3	23	11:15	0			0
31	30	202103000000	2021	3	23	11:45	0			0
32	31	202103000000	2021	3	23	12:15	0			0
33	32	202103000000	2021	3	23	12:45	0			0
34	33	202103000000	2021	3	23	13:15	0			0
35	34	202103000000	2021	3	23	13:45	0			0
36	35	202103000000	2021	3	23	14:15	0			0
37	36	202103000000	2021	3	23	14:45	0			0
38	37	202103000000	2021	3	23	15:15	0			0
39	38	202103000000	2021	3	23	15:45	0			0
40	39	202103000000	2021	3	23	16:15	0			0
41	40	202103000000	2021	3	23	16:45	0			0
42	41	202103000000	2021	3	23	17:15	0			0
43	42	202103000000	2021	3	23	17:45	0			0
SOS_MMS_2021_Camera1-D1_FromTW

Cell Formulas
Range	Formula
J2	J2	=SUM(INDIRECT("G"&ROW(F1)*3-1&":G"&ROW(F1)*3+1))
J3:J43	J3	=SUM(INDIRECT("G"&ROW(F3)*3-1&":G"&ROW(F3)*3+1))

 

[KRice]

Here is one approach...this counts the number of line entries. I'm not sure if any given line entry might have multiple sightings that need to be summed? If so, then some refinements are necessary.

Book2
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	Date	Time																									Total/Day
2		0:00	1:00	2:00	3:00	4:00	5:00	6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00	0:00
3		0.000	0.042	0.083	0.125	0.167	0.208	0.250	0.292	0.333	0.375	0.417	0.458	0.500	0.542	0.583	0.625	0.667	0.708	0.750	0.792	0.833	0.875	0.917	0.958	1
4	3/22/2021	0	0	0	0	0	0	0	0	0	0	2	2	2	2	2	2	2	2	1	0	0	0	0	0		17
5	3/23/2021	0	0	0	0	0	1	2	2	2	2	2	2	2	2	2	2	2	2	0	0	0	0	0	0		25
Sheet1

Cell Formulas
Range	Formula
M2:AK2	N2	=N3
M3:AK3	M3	=SEQUENCE(1,25,0,1/24)
L4:L5	L4	=UNIQUE(DATE(C2:C43,D2:D43,E2:E43))
M4:AJ5	M4	=SUM((DATE($C$2:$C$43,$D$2:$D$43,$E$2:$E$43)=$L4)*($F$2:$F$43>=M2)*($F$2:$F$43<N2))
AL4:AL5	AL4	=SUM(M4:AJ4)
Dynamic array formulas.