Sumifs Formula not working

[MAP77]

Hi all,
I have been using a SumIFs formula in other files, and it has worked fine. For some reason, when I use it in a new worksheet with different data but calculating the same thing, it doesn't work. I've triple-checked all the references and cannot figure out why it won't just work. My data goes down to row 3313.

The formula is to calculate the amount of kWh used in a day and night for each date. (Columns M and N).
Eg. M2 should equal D13: D49

Thanks guys.

Data File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	time	Month	Sum of Energy Consumed (kWh)	Sum of Air Conditioner (kWh)	Sum of Electric Hot Water (kWh)	Sum of EV Charger (kWh)	Period	5:30:00 AM	6:00:00 PM	Month	Date	Day	Night
2	23/04/2023	12:00:00 AM	April	0.04	0.0025039	0	0.0106435	Night			April	23/04/2023	0	0.035552
3	23/04/2023	12:30:00 AM	April	0.04	0.0025147	0	0.0106078	Night			May	24/04/2023	0	0
4	23/04/2023	1:00:00 AM	April	0.02	0.0024775	0	0.0081433	Night			June	25/04/2023	0	0
5	23/04/2023	1:30:00 AM	April	0.08	0.0025097	0.052	0.0077169	Night				26/04/2023	0	0
6	23/04/2023	2:00:00 AM	April	1.05	0.0029845	1.032	0.0084991	Night				27/04/2023	0	0
7	23/04/2023	2:30:00 AM	April	0.03	0.0024923	0	0.0077424	Night				28/04/2023	0	0
8	23/04/2023	3:00:00 AM	April	0.03	0.0024903	0	0.0076756	Night				29/04/2023	0	0
9	23/04/2023	3:30:00 AM	April	0.03	0.0024849	0	0.0076501	Night				30/04/2023	0	0
10	23/04/2023	4:00:00 AM	April	0.02	0.002484	0	0.008485	Night				1/05/2023	0	0
11	23/04/2023	4:30:00 AM	April	0.03	0.0024903	0	0.0090066	Night				2/05/2023	0	0
12	23/04/2023	5:00:00 AM	April	0.03	0.0024893	0	0.0082875	Night				3/05/2023	0	0
13	23/04/2023	5:30:00 AM	April	0.03	0.0024816	0	0.0079983	Day				4/05/2023	0	0
14	23/04/2023	6:00:00 AM	April	0.03	0.0024855	0	0.0077408	Day				5/05/2023	0	0
15	23/04/2023	6:30:00 AM	April	0.02	0.0024767	0	0.0075436	Day				6/05/2023	0	0
16	23/04/2023	7:00:00 AM	April	0.03	0.0024883	0	0.007641	Day				7/05/2023	0	0
17	23/04/2023	7:30:00 AM	April	0.03	0.0025206	0	0.0076639	Day				8/05/2023	0	0
18	23/04/2023	8:00:00 AM	April	0.03	0.0025217	0	0.0076587	Day				9/05/2023	0	0
19	23/04/2023	8:30:00 AM	April	0.03	0.002502	0	0.0074767	Day				10/05/2023	0	0
20	23/04/2023	9:00:00 AM	April	0.03	0.0025038	0	0.0074452	Day				11/05/2023	0	0
21	23/04/2023	9:30:00 AM	April	0.34	0.0026638	0.227	0.0081617	Day				12/05/2023	0	0
22	23/04/2023	10:00:00 AM	April	0.90	0.0029097	0.817	0.0084084	Day				13/05/2023	0	0
23	23/04/2023	10:30:00 AM	April	0.06	0.0025214	0	0.0162051	Day				14/05/2023	0	0
24	23/04/2023	11:00:00 AM	April	0.31	0.0026667	0	0.0166719	Day				15/05/2023	0	0
25	23/04/2023	11:30:00 AM	April	0.26	0.0026721	0	0.0158933	Day				16/05/2023	0	0
26	23/04/2023	12:00:00 PM	April	0.21	0.0026716	0	0.0142167	Day				17/05/2023	0	0
27	23/04/2023	12:30:00 PM	April	0.08	0.0025481	0	0.0077811	Day				18/05/2023	0	0
28	23/04/2023	1:00:00 PM	April	0.06	0.0025226	0	0.0075752	Day				19/05/2023	0	0
29	23/04/2023	1:30:00 PM	April	0.05	0.0025178	0	0.0117491	Day				20/05/2023	0	0
30	23/04/2023	2:00:00 PM	April	0.05	0.0025221	0	0.0081508	Day				21/05/2023	0	0
31	23/04/2023	2:30:00 PM	April	0.04	0.0025113	0	0.0074842	Day				22/05/2023	0	0
32	23/04/2023	3:00:00 PM	April	0.03	0.0025153	0	0.0074786	Day				23/05/2023		0
33	23/04/2023	3:30:00 PM	April	0.10	0.0025606	0	0.0078832	Day				24/05/2023		0
34	23/04/2023	4:00:00 PM	April	0.06	0.002532	0	0.0152734	Day				25/05/2023		0
35	23/04/2023	4:30:00 PM	April	0.06	0.0025228	0	0.0178857	Day				26/05/2023		0
36	23/04/2023	5:00:00 PM	April	0.06	0.0025164	0	0.0178766	Day				27/05/2023		0
37	23/04/2023	5:30:00 PM	April	0.04	0.0024981	0	0.0108217	Day				28/05/2023		0
38	23/04/2023	6:00:00 PM	April	0.03	0.0024884	0	0.0072872	Day				29/05/2023		0
39	23/04/2023	6:30:00 PM	April	0.03	0.0025013	0	0.0073062	Night				30/05/2023		0
40	23/04/2023	7:00:00 PM	April	0.03	0.002511	0	0.0073353	Night				31/05/2023		0
41	23/04/2023	7:30:00 PM	April	0.99	0.0029408	0.964	0.0079747	Night				1/06/2023		0
42	23/04/2023	8:00:00 PM	April	0.03	0.0024767	0.005	0.0072527	Night				2/06/2023		0
43	23/04/2023	8:30:00 PM	April	0.03	0.002488	0	0.0072628	Night				3/06/2023		0
44	23/04/2023	9:00:00 PM	April	0.13	0.0025794	0	0.0108732	Night				4/06/2023		0
45	23/04/2023	9:30:00 PM	April	0.08	0.0025532	0	0.0113871	Night				5/06/2023		0
46	23/04/2023	10:00:00 PM	April	1.81	0.003518	1.188	0.0162422	Night				6/06/2023		0
47	23/04/2023	10:30:00 PM	April	0.56	0.0028517	0.227	0.0147066	Night				7/06/2023		0
48	23/04/2023	11:00:00 PM	April	0.06	0.0025286	0	0.0166993	Night				8/06/2023		0
49	23/04/2023	11:30:00 PM	April	0.05	0.0025184	0	0.0244267	Night				9/06/2023		0
50	24/04/2023	12:00:00 AM	April	0.05	0.002525	0	0.0264217	Night				10/06/2023		0
51	24/04/2023	12:30:00 AM	April	0.04	0.0025183	0	0.0176224	Night				11/06/2023		0
52	24/04/2023	1:00:00 AM	April	0.03	0.0025171	0	0.0117561	Night				12/06/2023		0
53	24/04/2023	1:30:00 AM	April	0.03	0.0025099	0	0.0101822	Night				13/06/2023		0
54	24/04/2023	2:00:00 AM	April	0.03	0.0025242	0	0.0096495	Night				14/06/2023		0
55	24/04/2023	2:30:00 AM	April	0.03	0.0025203	0	0.009065	Night				15/06/2023		0
56	24/04/2023	3:00:00 AM	April	0.04	0.0025225	0	0.0104555	Night				16/06/2023		0
57	24/04/2023	3:30:00 AM	April	0.52	0.0027545	0.492	0.010187	Night				17/06/2023		0
58	24/04/2023	4:00:00 AM	April	0.58	0.0027759	0.561	0.0095283	Night				18/06/2023		0
59	24/04/2023	4:30:00 AM	April	0.03	0.00251	0	0.0088292	Night				19/06/2023		0
60	24/04/2023	5:00:00 AM	April	0.04	0.0025086	0	0.0089866	Night				20/06/2023		0
61	24/04/2023	5:30:00 AM	April	0.03	0.0025025	0	0.00862	Day				21/06/2023		0
62	24/04/2023	6:00:00 AM	April	0.03	0.0025075	0	0.008619	Day				22/06/2023		0
63	24/04/2023	6:30:00 AM	April	0.03	0.0024967	0	0.0089384	Day				23/06/2023		0
64	24/04/2023	7:00:00 AM	April	0.26	0.0026576	0	0.0089502	Day				24/06/2023		0
65	24/04/2023	7:30:00 AM	April	0.04	0.0025099	0	0.0081108	Day				25/06/2023		0
66	24/04/2023	8:00:00 AM	April	0.02	0.0024954	0	0.0075021	Day				26/06/2023		0
67	24/04/2023	8:30:00 AM	April	0.03	0.0025057	0	0.0073394	Day				27/06/2023		0
68	24/04/2023	9:00:00 AM	April	0.03	0.0025219	0	0.0073681	Day				28/06/2023		0
69	24/04/2023	9:30:00 AM	April	0.03	0.0025127	0	0.007348	Day				29/06/2023		0
70	24/04/2023	10:00:00 AM	April	0.03	0.0025	0	0.0073195	Day				30/06/2023	0	0
71	24/04/2023	10:30:00 AM	April	0.05	0.0025249	0	0.0088916	Day
72	24/04/2023	11:00:00 AM	April	0.06	0.0025365	0	0.0174994	Day
73	24/04/2023	11:30:00 AM	April	0.05	0.0025312	0	0.0180983	Day
74	24/04/2023	12:00:00 PM	April	0.11	0.0025808	0	0.0171009	Day
75	24/04/2023	12:30:00 PM	April	1.06	0.0029777	1.039	0.0118035	Day
76	24/04/2023	1:00:00 PM	April	0.03	0.0025136	0	0.0074745	Day
77	24/04/2023	1:30:00 PM	April	0.03	0.0025186	0	0.0074936	Day
78	24/04/2023	2:00:00 PM	April	0.03	0.0025087	0	0.0074808	Day
79	24/04/2023	2:30:00 PM	April	0.03	0.0025128	0	0.0074739	Day
80	24/04/2023	3:00:00 PM	April	0.03	0.0025112	0	0.0074822	Day
81	24/04/2023	3:30:00 PM	April	0.05	0.0025276	0	0.0084156	Day
82	24/04/2023	4:00:00 PM	April	0.10	0.0025795	0	0.0120772	Day
83	24/04/2023	4:30:00 PM	April	0.08	0.0025411	0	0.0158852	Day
84	24/04/2023	5:00:00 PM	April	0.09	0.0025458	0	0.0165709	Day
85	24/04/2023	5:30:00 PM	April	0.07	0.0025195	0	0.0087714	Day
86	24/04/2023	6:00:00 PM	April	0.12	0.0025594	0	0.0126284	Day
87	24/04/2023	6:30:00 PM	April	0.04	0.0025076	0	0.014265	Night
88	24/04/2023	7:00:00 PM	April	0.08	0.0025529	0	0.0147721	Night
89	24/04/2023	7:30:00 PM	April	0.97	0.0029555	0.91	0.0151069	Night
90	24/04/2023	8:00:00 PM	April	0.59	0.0027572	0.538	0.017772	Night
91	24/04/2023	8:30:00 PM	April	0.11	0.0025645	0	0.0547534	Night
92	24/04/2023	9:00:00 PM	April	0.05	0.0025264	0	0.0074469	Night
93	24/04/2023	9:30:00 PM	April	0.17	0.00263	0	0.0116686	Night
94	24/04/2023	10:00:00 PM	April	0.44	0.0028357	0	0.0146265	Night
95	24/04/2023	10:30:00 PM	April	1.77	0.0035222	1.105	0.1758211	Night
96	24/04/2023	11:00:00 PM	April	0.64	0.0029795	0	0.5959358	Night
97	24/04/2023	11:30:00 PM	April	0.63	0.0029677	0	0.5923983	Night
98	25/04/2023	12:00:00 AM	April	0.62	0.0029667	0	0.5872993	Night
99	25/04/2023	12:30:00 AM	April	0.62	0.0029713	0	0.5880947	Night
100	25/04/2023	1:00:00 AM	April	0.62	0.0029758	0	0.5874463	Night
101	25/04/2023	1:30:00 AM	April	0.62	0.0029772	0	0.58875	Night
102	25/04/2023	2:00:00 AM	April	0.62	0.0029797	0	0.5882947	Night
103	25/04/2023	2:30:00 AM	April	0.63	0.0029826	0	0.5894888	Night
104	25/04/2023	3:00:00 AM	April	0.62	0.0029544	0	0.5852794	Night
105	25/04/2023	3:30:00 AM	April	0.62	0.0029583	0	0.5848322	Night
106	25/04/2023	4:00:00 AM	April	0.62	0.0029553	0	0.5839621	Night
107	25/04/2023	4:30:00 AM	April	0.62	0.0029548	0	0.5832714	Night
108	25/04/2023	5:00:00 AM	April	1.04	0.0031461	0.43	0.5806494	Night
109	25/04/2023	5:30:00 AM	April	1.26	0.0032475	0.65	0.5799517	Day
110	25/04/2023	6:00:00 AM	April	0.62	0.0029475	0	0.5808722	Day
111	25/04/2023	6:30:00 AM	April	0.62	0.0029506	0	0.5809081	Day
112	25/04/2023	7:00:00 AM	April	0.56	0.002916	0	0.5242584	Day
113	25/04/2023	7:30:00 AM	April	0.03	0.0025206	0	0.008287	Day
114	25/04/2023	8:00:00 AM	April	0.03	0.0025148	0	0.0075339	Day
115	25/04/2023	8:30:00 AM	April	0.03	0.0025206	0	0.0074777	Day
116	25/04/2023	9:00:00 AM	April	0.03	0.0025088	0	0.0082486	Day
117	25/04/2023	9:30:00 AM	April	0.27	0.0026847	0	0.0109457	Day
118	25/04/2023	10:00:00 AM	April	0.04	0.0025383	0	0.0075778	Day
119	25/04/2023	10:30:00 AM	April	0.06	0.0025296	0	0.0118121	Day
120	25/04/2023	11:00:00 AM	April	0.15	0.0025889	0	0.0174284	Day
121	25/04/2023	11:30:00 AM	April	0.15	0.0025919	0	0.0120194	Day
122	25/04/2023	12:00:00 PM	April	0.07	0.0025286	0	0.0151272	Day
123	25/04/2023	12:30:00 PM	April	0.05	0.0025169	0	0.0146566	Day
124	25/04/2023	1:00:00 PM	April	0.07	0.0025392	0	0.0158785	Day
125	25/04/2023	1:30:00 PM	April	0.06	0.0025378	0	0.0095145	Day
126	25/04/2023	2:00:00 PM	April	0.05	0.0025292	0	0.0076191	Day
127	25/04/2023	2:30:00 PM	April	0.29	0.0026305	0.238	0.0077986	Day
128	25/04/2023	3:00:00 PM	April	0.82	0.0028761	0.729	0.00835	Day
129	25/04/2023	3:30:00 PM	April	0.07	0.0025331	0	0.0076531	Day
130	25/04/2023	4:00:00 PM	April	0.14	0.0025841	0	0.0143457	Day
131	25/04/2023	4:30:00 PM	April	0.08	0.0025272	0	0.0174089	Day
132	25/04/2023	5:00:00 PM	April	0.05	0.0024989	0	0.0090675	Day
133	25/04/2023	5:30:00 PM	April	0.11	0.0025455	0	0.008308	Day
134	25/04/2023	6:00:00 PM	April	0.09	0.0025361	0	0.0147016	Day
135	25/04/2023	6:30:00 PM	April	0.08	0.0025264	0	0.0164469	Night
136	25/04/2023	7:00:00 PM	April	0.03	0.002488	0	0.0072961	Night
137	25/04/2023	7:30:00 PM	April	0.03	0.0024803	0	0.0072905	Night
138	25/04/2023	8:00:00 PM	April	0.05	0.0025113	0	0.0086264	Night
139	25/04/2023	8:30:00 PM	April	0.88	0.0030498	0.455	0.0128424	Night
140	25/04/2023	9:00:00 PM	April	1.24	0.0032656	0.603	0.0143852	Night
141	25/04/2023	9:30:00 PM	April	0.13	0.0025866	0	0.0140178	Night
142	25/04/2023	10:00:00 PM	April	0.11	0.0025581	0	0.0214334	Night
143	25/04/2023	10:30:00 PM	April	0.08	0.002538	0	0.0179559	Night
144	25/04/2023	11:00:00 PM	April	0.06	0.0025252	0	0.0124389	Night
145	25/04/2023	11:30:00 PM	April	0.04	0.0025073	0	0.0110014	Night
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=UNIQUE(C2:C3313)
L2:L70	L2	=UNIQUE(TRUNC(A2:A3313))
M2	M2	=SUMIFS($D$2:$D$3313,$A$2:$A$3313,K2,$H$2:$H$3313,$M$1)
M3:M31	M3	=SUMIFS($D$2:$D$3313,$A$2:$A$3313,L3,$H$2:$H$3313,$M$1)
N2:N70	N2	=SUMIFS($D$2:$D$3313,$A$2:$A$3313,L2,$H$2:$H$3313,$N$1)
A3:A145	A3	=A2+TIME(0,30,0)
H2:H145	H2	=IF((B2>=$I$1)*(B2<=$J$1),"Day","Night")
Dynamic array formulas.

 

[RudRud]

M2 Criteria is it based in April or Date? Need to recheck criteria range 1 and criteria 1

Criteria 1 is it based in Time? Or month? If based in month then criteria range1 supposed to be in column C

 

[offthelip]

I think you have the wrong column in your formula: try this:

=SUMIFS($D$2:$D$3313,$C$2:$C$3313,K2,$H$2:$H$3313,$M$1)

 

[MAP77]

View attachment 94658

M2 Criteria is it based in April or Date? Need to recheck criteria range 1 and criteria 1

Criteria 1 is it based in Time? Or month? If based in month then criteria range1 supposed to be in column C

M2 is based on Date. Find all the Day values in D for the date in L2 and sum them. So we have a daily total of day usage (kWh)

 

[MAP77]

I think you have the wrong column in your formula: try this:

=SUMIFS($D$2:$D$3313,$C$2:$C$3313,K2,$H$2:$H$3313,$M$1)

Unfortunately, this doesn't work. It needs to add up all the kWh values for each date (day) in L that has 'day' written in column H.

 

[offthelip]

My mistake, just change column a to column c in your original formula. (not at pc at the moment)

 

[Dave Patton]

try

T202306a.xlsm
	A	B		D				H			K	L	M	N	O
1	Date	time		Sum of Energy Consumed (kWh)				Period			Month	Date	Day	Night	Total
2	Sun 23-Apr-23	0		0.0355519				Night			April	45039	2.9807442	5.2117842	8.192528
3	Sun 23-Apr-23	0.0208333		0.0350017				Night			0	45040	2.4728979	7.0143516	9.48725
4	Sun 23-Apr-23	0.0416667		0.0231852				Night				45041	7.2499433	8.4289927	15.78549
7c

Cell Formulas
Range	Formula
K2:K3	K2	=UNIQUE(C2:C3313)
M2:M4	M2	=SUMPRODUCT($D$2:$D$3313,--(INT($A$2:$A$3313)=$L2),--(H2:H3313=$M$1))
N2:N4	N2	=SUMPRODUCT($D$2:$D$3313,--(INT($A$2:$A$3313)=L2),--(H2:H3313=$N$1))
O2:O4	O2	=SUMPRODUCT($D$2:$D$145,--(INT($A$2:$A$145)=$L2))
Dynamic array formulas.

 

[Peter_SSs]

Or with FILTER

23 07 03.xlsm
	A	B		D				H				L	M	N
1	Date	time		Sum of Energy Consumed (kWh)				Period				Date	Day	Night
2	23/04/2023	12:00:00 AM		0.0355519				Night				23/04/2023	2.9807442	5.2117842
3	23/04/2023	12:30:00 AM		0.0350017				Night				24/04/2023	2.5586478	6.9286017
4	23/04/2023	1:00:00 AM		0.0231852				Night				25/04/2023	5.7970879	9.9884018
5	23/04/2023	1:30:00 AM		0.0791006				Night
6	23/04/2023	2:00:00 AM		1.0545319				Night
7	23/04/2023	2:30:00 AM		0.0320995				Night
8	23/04/2023	3:00:00 AM		0.0321379				Night
9	23/04/2023	3:30:00 AM		0.0284919				Night
10	23/04/2023	4:00:00 AM		0.0223245				Night
11	23/04/2023	4:30:00 AM		0.033248				Night
12	23/04/2023	5:00:00 AM		0.0324642				Night
13	23/04/2023	5:30:00 AM		0.0320961				Day
14	23/04/2023	6:00:00 AM		0.0282338				Day
15	23/04/2023	6:30:00 AM		0.0214574				Day
16	23/04/2023	7:00:00 AM		0.0316914				Day
17	23/04/2023	7:30:00 AM		0.0321544				Day
18	23/04/2023	8:00:00 AM		0.0322769				Day
19	23/04/2023	8:30:00 AM		0.0286294				Day
20	23/04/2023	9:00:00 AM		0.0311428				Day
21	23/04/2023	9:30:00 AM		0.3393994				Day
22	23/04/2023	10:00:00 AM		0.9023672				Day
23	23/04/2023	10:30:00 AM		0.0565942				Day
24	23/04/2023	11:00:00 AM		0.309108				Day
25	23/04/2023	11:30:00 AM		0.2623462				Day
26	23/04/2023	12:00:00 PM		0.2149911				Day
27	23/04/2023	12:30:00 PM		0.0828918				Day
28	23/04/2023	1:00:00 PM		0.0569965				Day
29	23/04/2023	1:30:00 PM		0.0542099				Day
30	23/04/2023	2:00:00 PM		0.0498295				Day
31	23/04/2023	2:30:00 PM		0.0383294				Day
32	23/04/2023	3:00:00 PM		0.0330228				Day
33	23/04/2023	3:30:00 PM		0.0967434				Day
34	23/04/2023	4:00:00 PM		0.0578764				Day
35	23/04/2023	4:30:00 PM		0.0565602				Day
36	23/04/2023	5:00:00 PM		0.0562293				Day
37	23/04/2023	5:30:00 PM		0.0430804				Day
38	23/04/2023	6:00:00 PM		0.0324863				Day
39	23/04/2023	6:30:00 PM		0.0324419				Night
40	23/04/2023	7:00:00 PM		0.0323833				Night
41	23/04/2023	7:30:00 PM		0.9860217				Night
42	23/04/2023	8:00:00 PM		0.0336654				Night
43	23/04/2023	8:30:00 PM		0.027637				Night
44	23/04/2023	9:00:00 PM		0.1346338				Night
45	23/04/2023	9:30:00 PM		0.0819659				Night
46	23/04/2023	10:00:00 PM		1.8085687				Night
47	23/04/2023	10:30:00 PM		0.5584434				Night
48	23/04/2023	11:00:00 PM		0.0573794				Night
49	23/04/2023	11:30:00 PM		0.0505064				Night
50	24/04/2023	12:00:00 AM		0.0519052				Night
51	24/04/2023	12:30:00 AM		0.0430055				Night
52	24/04/2023	1:00:00 AM		0.0309592				Night
53	24/04/2023	1:30:00 AM		0.0268022				Night
54	24/04/2023	2:00:00 AM		0.0348959				Night
55	24/04/2023	2:30:00 AM		0.034239				Night
56	24/04/2023	3:00:00 AM		0.0352024				Night
57	24/04/2023	3:30:00 AM		0.5215545				Night
58	24/04/2023	4:00:00 AM		0.5799589				Night
59	24/04/2023	4:30:00 AM		0.0282641				Night
60	24/04/2023	5:00:00 AM		0.0350686				Night
61	24/04/2023	5:30:00 AM		0.03316				Day
62	24/04/2023	6:00:00 AM		0.0332616				Day
63	24/04/2023	6:30:00 AM		0.0315303				Day
64	24/04/2023	7:00:00 AM		0.2552311				Day
65	24/04/2023	7:30:00 AM		0.035232				Day
66	24/04/2023	8:00:00 AM		0.023317				Day
67	24/04/2023	8:30:00 AM		0.0323566				Day
68	24/04/2023	9:00:00 AM		0.0324556				Day
69	24/04/2023	9:30:00 AM		0.0324587				Day
70	24/04/2023	10:00:00 AM		0.0322083				Day
71	24/04/2023	10:30:00 AM		0.0534659				Day
72	24/04/2023	11:00:00 AM		0.0576323				Day
73	24/04/2023	11:30:00 AM		0.0538605				Day
74	24/04/2023	12:00:00 PM		0.1101797				Day
75	24/04/2023	12:30:00 PM		1.0638283				Day
76	24/04/2023	1:00:00 PM		0.0311013				Day
77	24/04/2023	1:30:00 PM		0.0332243				Day
78	24/04/2023	2:00:00 PM		0.0331147				Day
79	24/04/2023	2:30:00 PM		0.0329457				Day
80	24/04/2023	3:00:00 PM		0.0330121				Day
81	24/04/2023	3:30:00 PM		0.0533725				Day
82	24/04/2023	4:00:00 PM		0.1044134				Day
83	24/04/2023	4:30:00 PM		0.0809677				Day
84	24/04/2023	5:00:00 PM		0.0871692				Day
85	24/04/2023	5:30:00 PM		0.0683305				Day
86	24/04/2023	6:00:00 PM		0.1208185				Day
87	24/04/2023	6:30:00 PM		0.038868				Night
88	24/04/2023	7:00:00 PM		0.0814403				Night
89	24/04/2023	7:30:00 PM		0.9736425				Night
90	24/04/2023	8:00:00 PM		0.5946882				Night
91	24/04/2023	8:30:00 PM		0.1064112				Night
92	24/04/2023	9:00:00 PM		0.0510053				Night
93	24/04/2023	9:30:00 PM		0.1746814				Night
94	24/04/2023	10:00:00 PM		0.4366231				Night
95	24/04/2023	10:30:00 PM		1.7741065				Night
96	24/04/2023	11:00:00 PM		0.6422853				Night
97	24/04/2023	11:30:00 PM		0.6329944				Night
98	25/04/2023	12:00:00 AM		0.6231087				Night
99	25/04/2023	12:30:00 AM		0.6237908				Night
100	25/04/2023	1:00:00 AM		0.622918				Night
101	25/04/2023	1:30:00 AM		0.6243223				Night
102	25/04/2023	2:00:00 AM		0.6238122				Night
103	25/04/2023	2:30:00 AM		0.6250102				Night
104	25/04/2023	3:00:00 AM		0.6205129				Night
105	25/04/2023	3:30:00 AM		0.620018				Night
106	25/04/2023	4:00:00 AM		0.6191855				Night
107	25/04/2023	4:30:00 AM		0.6184446				Night
108	25/04/2023	5:00:00 AM		1.0412525				Night
109	25/04/2023	5:30:00 AM		1.2580042				Day
110	25/04/2023	6:00:00 AM		0.616047				Day
111	25/04/2023	6:30:00 AM		0.6153449				Day
112	25/04/2023	7:00:00 AM		0.5607891				Day
113	25/04/2023	7:30:00 AM		0.027825				Day
114	25/04/2023	8:00:00 AM		0.0262383				Day
115	25/04/2023	8:30:00 AM		0.0297253				Day
116	25/04/2023	9:00:00 AM		0.0323453				Day
117	25/04/2023	9:30:00 AM		0.2671201				Day
118	25/04/2023	10:00:00 AM		0.0432822				Day
119	25/04/2023	10:30:00 AM		0.0600837				Day
120	25/04/2023	11:00:00 AM		0.1507834				Day
121	25/04/2023	11:30:00 AM		0.1455842				Day
122	25/04/2023	12:00:00 PM		0.0679084				Day
123	25/04/2023	12:30:00 PM		0.0536037				Day
124	25/04/2023	1:00:00 PM		0.073528				Day
125	25/04/2023	1:30:00 PM		0.0568942				Day
126	25/04/2023	2:00:00 PM		0.050928				Day
127	25/04/2023	2:30:00 PM		0.2922994				Day
128	25/04/2023	3:00:00 PM		0.8228866				Day
129	25/04/2023	3:30:00 PM		0.0689505				Day
130	25/04/2023	4:00:00 PM		0.1413963				Day
131	25/04/2023	4:30:00 PM		0.0772126				Day
132	25/04/2023	5:00:00 PM		0.0514658				Day
133	25/04/2023	5:30:00 PM		0.1129109				Day
134	25/04/2023	6:00:00 PM		0.0939308				Day
135	25/04/2023	6:30:00 PM		0.0773451				Night
136	25/04/2023	7:00:00 PM		0.0298794				Night
137	25/04/2023	7:30:00 PM		0.0296431				Night
138	25/04/2023	8:00:00 PM		0.0458624				Night
139	25/04/2023	8:30:00 PM		0.8780084				Night
140	25/04/2023	9:00:00 PM		1.2371673				Night
141	25/04/2023	9:30:00 PM		0.1330808				Night
142	25/04/2023	10:00:00 PM		0.1079059				Night
143	25/04/2023	10:30:00 PM		0.08058				Night
144	25/04/2023	11:00:00 PM		0.0644748				Night
145	25/04/2023	11:30:00 PM		0.0420789				Night
kWh

Cell Formulas
Range	Formula
M2:N4	M2	=SUM(FILTER($D$2:$D$5000,(INT($A$2:$A$5000)=$L2)*($H$2:$H$5000=M$1)))

 

[MAP77]

Or with FILTER

23 07 03.xlsm
	A	B		D				H				L	M	N
1	Date	time		Sum of Energy Consumed (kWh)				Period				Date	Day	Night
2	23/04/2023	12:00:00 AM		0.0355519				Night				23/04/2023	2.9807442	5.2117842
3	23/04/2023	12:30:00 AM		0.0350017				Night				24/04/2023	2.5586478	6.9286017
4	23/04/2023	1:00:00 AM		0.0231852				Night				25/04/2023	5.7970879	9.9884018
5	23/04/2023	1:30:00 AM		0.0791006				Night
6	23/04/2023	2:00:00 AM		1.0545319				Night
7	23/04/2023	2:30:00 AM		0.0320995				Night
8	23/04/2023	3:00:00 AM		0.0321379				Night
9	23/04/2023	3:30:00 AM		0.0284919				Night
10	23/04/2023	4:00:00 AM		0.0223245				Night
11	23/04/2023	4:30:00 AM		0.033248				Night
12	23/04/2023	5:00:00 AM		0.0324642				Night
13	23/04/2023	5:30:00 AM		0.0320961				Day
14	23/04/2023	6:00:00 AM		0.0282338				Day
15	23/04/2023	6:30:00 AM		0.0214574				Day
16	23/04/2023	7:00:00 AM		0.0316914				Day
17	23/04/2023	7:30:00 AM		0.0321544				Day
18	23/04/2023	8:00:00 AM		0.0322769				Day
19	23/04/2023	8:30:00 AM		0.0286294				Day
20	23/04/2023	9:00:00 AM		0.0311428				Day
21	23/04/2023	9:30:00 AM		0.3393994				Day
22	23/04/2023	10:00:00 AM		0.9023672				Day
23	23/04/2023	10:30:00 AM		0.0565942				Day
24	23/04/2023	11:00:00 AM		0.309108				Day
25	23/04/2023	11:30:00 AM		0.2623462				Day
26	23/04/2023	12:00:00 PM		0.2149911				Day
27	23/04/2023	12:30:00 PM		0.0828918				Day
28	23/04/2023	1:00:00 PM		0.0569965				Day
29	23/04/2023	1:30:00 PM		0.0542099				Day
30	23/04/2023	2:00:00 PM		0.0498295				Day
31	23/04/2023	2:30:00 PM		0.0383294				Day
32	23/04/2023	3:00:00 PM		0.0330228				Day
33	23/04/2023	3:30:00 PM		0.0967434				Day
34	23/04/2023	4:00:00 PM		0.0578764				Day
35	23/04/2023	4:30:00 PM		0.0565602				Day
36	23/04/2023	5:00:00 PM		0.0562293				Day
37	23/04/2023	5:30:00 PM		0.0430804				Day
38	23/04/2023	6:00:00 PM		0.0324863				Day
39	23/04/2023	6:30:00 PM		0.0324419				Night
40	23/04/2023	7:00:00 PM		0.0323833				Night
41	23/04/2023	7:30:00 PM		0.9860217				Night
42	23/04/2023	8:00:00 PM		0.0336654				Night
43	23/04/2023	8:30:00 PM		0.027637				Night
44	23/04/2023	9:00:00 PM		0.1346338				Night
45	23/04/2023	9:30:00 PM		0.0819659				Night
46	23/04/2023	10:00:00 PM		1.8085687				Night
47	23/04/2023	10:30:00 PM		0.5584434				Night
48	23/04/2023	11:00:00 PM		0.0573794				Night
49	23/04/2023	11:30:00 PM		0.0505064				Night
50	24/04/2023	12:00:00 AM		0.0519052				Night
51	24/04/2023	12:30:00 AM		0.0430055				Night
52	24/04/2023	1:00:00 AM		0.0309592				Night
53	24/04/2023	1:30:00 AM		0.0268022				Night
54	24/04/2023	2:00:00 AM		0.0348959				Night
55	24/04/2023	2:30:00 AM		0.034239				Night
56	24/04/2023	3:00:00 AM		0.0352024				Night
57	24/04/2023	3:30:00 AM		0.5215545				Night
58	24/04/2023	4:00:00 AM		0.5799589				Night
59	24/04/2023	4:30:00 AM		0.0282641				Night
60	24/04/2023	5:00:00 AM		0.0350686				Night
61	24/04/2023	5:30:00 AM		0.03316				Day
62	24/04/2023	6:00:00 AM		0.0332616				Day
63	24/04/2023	6:30:00 AM		0.0315303				Day
64	24/04/2023	7:00:00 AM		0.2552311				Day
65	24/04/2023	7:30:00 AM		0.035232				Day
66	24/04/2023	8:00:00 AM		0.023317				Day
67	24/04/2023	8:30:00 AM		0.0323566				Day
68	24/04/2023	9:00:00 AM		0.0324556				Day
69	24/04/2023	9:30:00 AM		0.0324587				Day
70	24/04/2023	10:00:00 AM		0.0322083				Day
71	24/04/2023	10:30:00 AM		0.0534659				Day
72	24/04/2023	11:00:00 AM		0.0576323				Day
73	24/04/2023	11:30:00 AM		0.0538605				Day
74	24/04/2023	12:00:00 PM		0.1101797				Day
75	24/04/2023	12:30:00 PM		1.0638283				Day
76	24/04/2023	1:00:00 PM		0.0311013				Day
77	24/04/2023	1:30:00 PM		0.0332243				Day
78	24/04/2023	2:00:00 PM		0.0331147				Day
79	24/04/2023	2:30:00 PM		0.0329457				Day
80	24/04/2023	3:00:00 PM		0.0330121				Day
81	24/04/2023	3:30:00 PM		0.0533725				Day
82	24/04/2023	4:00:00 PM		0.1044134				Day
83	24/04/2023	4:30:00 PM		0.0809677				Day
84	24/04/2023	5:00:00 PM		0.0871692				Day
85	24/04/2023	5:30:00 PM		0.0683305				Day
86	24/04/2023	6:00:00 PM		0.1208185				Day
87	24/04/2023	6:30:00 PM		0.038868				Night
88	24/04/2023	7:00:00 PM		0.0814403				Night
89	24/04/2023	7:30:00 PM		0.9736425				Night
90	24/04/2023	8:00:00 PM		0.5946882				Night
91	24/04/2023	8:30:00 PM		0.1064112				Night
92	24/04/2023	9:00:00 PM		0.0510053				Night
93	24/04/2023	9:30:00 PM		0.1746814				Night
94	24/04/2023	10:00:00 PM		0.4366231				Night
95	24/04/2023	10:30:00 PM		1.7741065				Night
96	24/04/2023	11:00:00 PM		0.6422853				Night
97	24/04/2023	11:30:00 PM		0.6329944				Night
98	25/04/2023	12:00:00 AM		0.6231087				Night
99	25/04/2023	12:30:00 AM		0.6237908				Night
100	25/04/2023	1:00:00 AM		0.622918				Night
101	25/04/2023	1:30:00 AM		0.6243223				Night
102	25/04/2023	2:00:00 AM		0.6238122				Night
103	25/04/2023	2:30:00 AM		0.6250102				Night
104	25/04/2023	3:00:00 AM		0.6205129				Night
105	25/04/2023	3:30:00 AM		0.620018				Night
106	25/04/2023	4:00:00 AM		0.6191855				Night
107	25/04/2023	4:30:00 AM		0.6184446				Night
108	25/04/2023	5:00:00 AM		1.0412525				Night
109	25/04/2023	5:30:00 AM		1.2580042				Day
110	25/04/2023	6:00:00 AM		0.616047				Day
111	25/04/2023	6:30:00 AM		0.6153449				Day
112	25/04/2023	7:00:00 AM		0.5607891				Day
113	25/04/2023	7:30:00 AM		0.027825				Day
114	25/04/2023	8:00:00 AM		0.0262383				Day
115	25/04/2023	8:30:00 AM		0.0297253				Day
116	25/04/2023	9:00:00 AM		0.0323453				Day
117	25/04/2023	9:30:00 AM		0.2671201				Day
118	25/04/2023	10:00:00 AM		0.0432822				Day
119	25/04/2023	10:30:00 AM		0.0600837				Day
120	25/04/2023	11:00:00 AM		0.1507834				Day
121	25/04/2023	11:30:00 AM		0.1455842				Day
122	25/04/2023	12:00:00 PM		0.0679084				Day
123	25/04/2023	12:30:00 PM		0.0536037				Day
124	25/04/2023	1:00:00 PM		0.073528				Day
125	25/04/2023	1:30:00 PM		0.0568942				Day
126	25/04/2023	2:00:00 PM		0.050928				Day
127	25/04/2023	2:30:00 PM		0.2922994				Day
128	25/04/2023	3:00:00 PM		0.8228866				Day
129	25/04/2023	3:30:00 PM		0.0689505				Day
130	25/04/2023	4:00:00 PM		0.1413963				Day
131	25/04/2023	4:30:00 PM		0.0772126				Day
132	25/04/2023	5:00:00 PM		0.0514658				Day
133	25/04/2023	5:30:00 PM		0.1129109				Day
134	25/04/2023	6:00:00 PM		0.0939308				Day
135	25/04/2023	6:30:00 PM		0.0773451				Night
136	25/04/2023	7:00:00 PM		0.0298794				Night
137	25/04/2023	7:30:00 PM		0.0296431				Night
138	25/04/2023	8:00:00 PM		0.0458624				Night
139	25/04/2023	8:30:00 PM		0.8780084				Night
140	25/04/2023	9:00:00 PM		1.2371673				Night
141	25/04/2023	9:30:00 PM		0.1330808				Night
142	25/04/2023	10:00:00 PM		0.1079059				Night
143	25/04/2023	10:30:00 PM		0.08058				Night
144	25/04/2023	11:00:00 PM		0.0644748				Night
145	25/04/2023	11:30:00 PM		0.0420789				Night
kWh

Cell Formulas
Range	Formula
M2:N4	M2	=SUM(FILTER($D$2:$D$5000,(INT($A$2:$A$5000)=$L2)*($H$2:$H$5000=M$1)))

This worked better than the SUMPRODUCT option. I just don't understand why my Sumifs formula stopped working for me.

 

[MAP77]

My mistake, just change column a to column c in your original formula. (not at pc at the moment)

Sorry, this didn't work either.