How do I write SUMIFS statement to sum up salaries with multiple criteria?

[maxim642]

Data begins in cell H1

I would like to sum up salaries for people within a current headcount. The headcount is based on the date in H1.

Employees should only be summed if they match the following criteria:
Start date is <=date in H1 & Termination Date is >=date in H1 OR Termination Date is blank (if it's blank, they are still with the company). Each sum should include criteria that tie it to each of the x3 divisions (A, B, C).

The cell below is H1

30/06/2021								$
	Employee	Start Date	Termination Date	Salary	Division		A
	1	12/01/2020	26/05/2021	$ 58,600	A		B
	2	12/06/2020		$ 23,583	B		C
	3	30/11/2021		$ 22,562	C
	4	02/07/2020	29/06/2021	$ 21,178	B
	5	08/03/2019	30/06/2021	$ 25,238	B
	6	07/06/2021		$ 67,917	A
	7	09/08/2021		$ 18,930	A
	8	08/04/2016	18/09/2021	$ 47,116	C
	9	09/05/2013	14/11/2021	$ 55,164	C
	10	09/09/2021		$ 19,124	C

 

[Fluff]

How about

=SUM(FILTER($L$3:$L$12,($M$3:$M$12=O2)*($J$3:$J$12<=$H$1)*(($L$3:$L$12>=$H$1)+($L$3:$L$12=""))))

 

[maxim642]

Thanks, but it wasn't quite totalling as it should - and when I attempted to drag the formula down, it ceased working altogether. I'll share 2 worksheets...the first is the sample dataset and the second will be the summary sheet where I hope to input the formulas.

Also, I don't think the initial formula was summing up the salaries as intended. I may have explained it poorly. Basically, I want to know the total salary for employees in the business based on a set date (in a cell found on the 2nd worksheet), split by division.

If their hire date is before or equal to the date in the worksheet AND the terminated date is blank or for a date equal to or beyond the worksheet's date, they should be counted and their salary summed up.

Master_Template(27).xlsx
	A	B	C	D	E
1	Employee	Hire Date	Termination Date	Division	Salary
2	833	7/7/21		COO	71,621
3	841	7/6/21		CCO	85,544
4	850	7/6/21		CCO	26,653
5	1445	10/16/19		CTO	42,325
6	1302	7/22/19		CFO	13,490
7	1347	7/22/19		CCRO	82,806
8	716	7/17/19		CISO	25,503
9	746	7/17/19		CISO	55,210
10	1331	7/11/19		CCRO	90,336
11	1094	7/8/19		CTO	87,078
12	1199	7/8/19		CAO	11,657
13	1120	7/4/19		COO	62,593
14	1176	7/1/19		CTO	48,740
15	1198	7/1/19		CFO	32,965
16	1175	6/27/19		CTO	25,949
17	1276	6/25/19		CCO	26,102
18	1325	6/17/19		CCRO	29,920
19	1442	6/17/19		CTO	10,570
20	1266	6/17/19		COO	17,843
21	1224	6/3/19		CD	57,269
22	1093	5/27/19		CTO	54,144
23	1314	5/27/19		CCRO	44,244
24	1324	5/27/19		CCRO	81,952
25	159	5/6/19		CEO	56,336
26	1316	4/22/19		CCRO	12,059
27	1171	4/15/19		Shareholder	23,718
28	306	4/15/19		CEO	60,937
29	1275	4/12/19		CCO	23,033
30	1274	4/8/19		CCO	40,237
31	1312	4/1/19		CD	81,797
32	1223	3/18/19		CD	11,505
33	1342	3/18/19		CCRO	80,005
34	1119	3/11/19		Shareholder	86,631
35	1400	3/11/19		CFO	61,920
36	1197	3/4/19		CTO	76,418
37	1399	2/25/19		CCRO	48,198
38	1088	2/25/19		CTO	25,676
39	1398	2/18/19		CAO	91,181
40	1397	2/18/19		CCRO	15,458
41	1191	2/11/19		CCO	72,146
42	1345	2/1/19		CCRO	87,929
43	1319	1/14/19		CCRO	70,840
44	1234	1/14/19		CCO	54,494
45	1118	1/5/19		Shareholder	37,064
46	1396	1/1/19		CAO	51,313
47	1308	1/1/19		CCO	92,443
48	1222	12/17/18		CD	66,539
49	263	12/12/18		CTO	46,661
50	1273	12/10/18		CCO	57,503
51	1272	12/3/18		CCO	12,487
52	1395	12/1/18		CAO	60,918
53	1437	11/26/18		CTO	22,910
54	1426	11/23/18		CCO	70,071
55	269	11/14/18		CCRO	48,953
56	1189	11/12/18		CCO	33,675
57	216	11/12/18		CTO	16,626
58	1085	11/5/18		CEO	89,275
59	1083	10/15/18		CCO	88,036
60	1285	10/10/18		CCO	72,130
61	1414	10/8/18		CCRO	21,358
62	1441	10/1/18		CTO	16,929
63	1078	9/24/18		CCRO	61,475
64	1338	9/18/18		CTO	84,438
65	1313	9/13/18		CCRO	69,814
66	1220	9/10/18		CTO	27,209
67	707	9/10/18		CISO	89,414
68	1394	9/10/18		CCRO	55,274
69	1393	9/10/18		CTO	12,371
70	1435	9/4/18		CTO	15,623
71	1364	9/3/18		CCRO	29,324
72	1213	8/27/18		Shareholder	56,870
73	1219	8/27/18		CTO	79,862
74	1334	8/16/18		CCRO	20,251
75	1392	8/9/18		CCRO	92,263
76	1361	8/8/18		CCRO	79,516
77	266	8/6/18		CCRO	12,825
78	1336	8/2/18		CCRO	58,281
79	1390	8/1/18		CCO	54,830
80	1389	8/1/18		CCO	74,803
81	1391	8/1/18		CCRO	21,426
82	1076	7/30/18		CD	95,769
83	1337	7/18/18		CCRO	62,303
84	1332	7/17/18		CTO	50,460
85	1436	7/16/18		CTO	56,373
86	1072	7/16/18		CD	18,781
87	1177	7/9/18		CISO	92,286
88	1231	7/2/18		CCO	39,645
89	1073	7/2/18		CAO	16,402
90	1406	7/2/18		CAO	79,010
91	1265	7/1/18		CCO	61,260
92	420	6/28/18		CCRO	99,524
93	740	6/18/18		CISO	60,275
94	1386	6/18/18		CCRO	15,437
95	1387	6/18/18		CCRO	60,702
96	1069	6/12/18		CCO	24,870
97	1356	6/6/18		CFO	22,612
98	1142	6/1/18		CCO	51,290
99	1352	5/21/18		CCRO	60,226
100	1186	5/4/18		CTO	32,828
101	1415	5/3/18		CCRO	86,419
102	1384	5/1/18		CFO	14,436
103	1071	4/25/18		CTO	14,001
104	314	4/16/18		CTO	96,247
105	1066	4/10/18		CCO	92,448
106	727	4/2/18		CISO	36,046
107	734	3/22/18		CISO	68,283
108	1114	3/19/18		CCO	85,101
109	1089	3/19/18		CTO	39,619
110	1	3/1/18		CEO	62,725
111	1263	2/23/18		CCO	66,784
112	1112	2/8/18		Shareholder	41,021
113	1233	2/6/18		CCO	76,707
114	1382	2/5/18		CCO	52,177
115	233	2/5/18		COO	68,561
116	1262	2/5/18		CD	72,895
117	1383	2/2/18		CCRO	62,350
118	1405	1/22/18		CCO	83,798
119	1063	1/4/18		Shareholder	44,019
120	722	12/25/17		CISO	66,759
121	1327	12/20/17		CCRO	46,927
122	416	12/18/17		CTO	47,127
123	1291	12/5/17		CCO	37,015
124	1235	11/30/17		CCRO	51,502
125	398	11/13/17		CTO	91,376
126	1381	11/13/17		CCRO	23,549
127	1059	10/31/17		CAO	70,309
128	1299	10/16/17		COO	61,419
129	1218	10/12/17		CD	93,603
130	1346	10/3/17		CCRO	21,041
131	733	10/2/17		CISO	81,106
132	1351	10/2/17		CCRO	50,015
133	1054	9/29/17		CCO	45,792
134	1055	9/29/17		CCO	39,325
135	1053	9/26/17		CTO	39,461
136	1052	9/18/17		CAO	92,050
137	1051	9/18/17		CEO	23,545
138	1111	9/1/17		Shareholder	42,240
139	1341	9/1/17		CCRO	18,455
140	1261	8/31/17		COO	96,320
141	1424	8/22/17		CCO	28,514
142	1050	8/21/17		CEO	38,947
143	1150	8/16/17		CCO	54,494
144	1357	8/8/17		CTO	14,928
145	1230	7/24/17		CCO	83,350
146	1269	7/17/17		CCO	15,003
147	169	7/3/17		CTO	10,033
148	1358	7/3/17		CD	22,616
149	1049	7/1/17		COO	50,021
150	1133	6/26/17		CCRO	99,368
151	1229	6/23/17		CCO	14,798
152	1413	6/7/17		CCRO	32,741
153	1379	6/1/17		CCO	10,846
154	1380	6/1/17		CCO	24,205
155	1282	6/1/17		CCO	93,676
156	1131	5/29/17		CCO	34,486
157	1412	5/25/17		CCRO	26,302
158	730	5/22/17		CISO	90,866
159	1048	5/17/17		CCO	30,871
160	1062	5/15/17		CTO	37,021
161	1344	5/10/17		CD	37,125
162	1185	5/2/17		CCO	65,467
163	1349	4/3/17		CCRO	65,295
164	1448	4/1/17		CCO	40,786
165	1170	3/28/17		CCRO	29,962
166	1047	3/13/17		CCO	33,605
167	700	3/13/17		CISO	33,063
168	1109	3/9/17		Shareholder	86,839
169	1169	3/6/17		CISO	75,625
170	1211	3/1/17		CTO	74,562
171	319	2/20/17		COO	84,913
172	422	2/6/17		CTO	31,674
173	1411	1/19/17		CCRO	82,999
174	1300	1/2/17		CTO	97,087
175	1378	12/19/16		CAO	84,650
176	244	12/12/16		CTO	73,739
177	701	12/5/16		CISO	46,518
178	750	11/15/16		CISO	60,307
179	1210	10/20/16		CTO	89,842
180	1268	10/18/16		CCO	23,782
181	1044	10/18/16		CD	98,197
182	217	10/17/16		CTO	44,004
183	1041	10/10/16		CEO	31,635
184	1043	10/8/16		Shareholder	39,484
185	1207	10/7/16		CTO	45,534
186	312	9/26/16		CCRO	21,195
187	1040	9/19/16		CCO	76,233
188	1305	9/19/16		CCO	36,417
189	1108	9/5/16		CTO	77,589
190	1377	8/8/16		CCO	17,215
191	1321	8/1/16		CFO	91,627
192	1355	8/1/16		COO	24,553
193	1039	7/20/16		Shareholder	49,819
194	1038	7/18/16		Shareholder	45,009
195	1410	7/15/16		CCO	14,393
196	1447	7/4/16		CCRO	64,561
197	709	6/27/16		CISO	87,960
198	1339	6/13/16		CCRO	93,797
199	1129	6/7/16		CTO	99,522
200	1037	6/6/16		CTO	47,446
201	238	6/2/16		CCRO	56,473
202	1035	5/3/16		CCRO	47,442
203	1034	4/25/16		CCRO	53,648
204	1328	4/4/16		CAO	38,510
205	1217	3/7/16		CD	38,308
206	1033	3/1/16		CTO	24,472
207	1216	2/15/16		CD	50,956
208	702	2/12/16		CISO	79,847
209	739	1/13/16		CISO	90,278
210	703	12/21/15		CISO	55,151
211	1032	12/2/15		CCRO	19,772
212	1182	12/1/15		CCO	36,493
213	1031	11/27/15		CCO	62,547
214	1407	10/14/15		CCRO	38,439
215	1408	10/12/15		CCO	85,490
216	1180	10/12/15		CTO	85,530
217	1286	10/1/15		CCO	30,177
218	1167	9/23/15		CCO	68,251
219	155	9/15/15		CTO	68,042
220	1166	9/1/15		CTO	41,354
221	1164	8/26/15		CTO	20,075
222	1232	8/25/15		CCRO	79,891
223	1270	8/11/15		CCO	14,272
224	168	8/1/15		CTO	49,464
225	1029	7/2/15		CCO	60,689
226	1057	6/23/15		CCO	47,755
227	1271	6/22/15		CCO	94,336
228	1027	6/17/15		CCO	16,846
229	704	6/17/15		CISO	14,185
230	749	6/1/15		CISO	31,053
231	1028	5/26/15		CCO	43,525
232	1215	5/4/15		CD	73,013
233	158	4/23/15		CTO	89,277
234	1304	4/6/15		CCO	71,608
235	1135	4/3/15		CCO	10,907
236	1208	3/16/15		CTO	88,526
237	1127	3/2/15		CTO	64,481
238	1136	1/12/15		CCO	13,377
239	151	1/9/15		CTO	60,009
240	1125	1/8/15		CCO	44,077
241	1168	1/1/15		CTO	33,570
242	1056	12/12/14		CAO	56,274
243	1104	12/1/14		CTO	33,229
244	1022	11/17/14		CTO	73,645
245	1021	11/3/14		CCRO	72,566
246	711	10/31/14		CISO	63,671
247	1020	10/14/14		CD	10,373
248	1017	9/22/14		COO	98,790
249	276	4/10/12		CTO	34,104
250	1372	11/19/11		CCO	26,609
251	1370	10/31/11		CCRO	13,251
252	391	10/10/11		CEO	88,967
253	1103	7/27/11		Shareholder	34,695
254	1003	4/27/11		CTO	52,926
255	369	3/14/11		CTO	49,389
256	359	1/26/11		CTO	43,392
257	1369	1/10/11		CCRO	15,796
258	1367	1/3/11		CTO	85,667
259	259	11/1/10		CISO	62,320
260	742	9/27/10		CISO	59,184
261	210	9/1/10		CTO	69,344
262	1366	8/4/10		CCO	81,150
263	255	5/31/10		CTO	20,353
264	410	4/12/10		CTO	45,837
265	723	10/18/09		CISO	92,970
266	1368	9/5/09		CCRO	87,243
267	309	6/29/09		CEO	59,507
268	726	2/16/09		CISO	88,227
269	223	9/30/08		CISO	54,990
270	732	3/17/08		CISO	13,093
271	377	9/14/07		CISO	85,199
272	321	11/1/06		CISO	12,207
273	219	9/1/03		CTO	32,636
274	190	1/1/90			17,256
275	837	6/28/21	7/6/21	CTO	73,380
276	786	5/3/21	7/8/21	CCRO	58,013
277	725	4/7/21	5/21/21	CCRO	22,829
278	765	3/29/21		Shareholder	39,594
279	763	3/24/21	5/24/21	CISO	31,823
280	753	3/23/21	4/22/21	CCRO	67,409
281	756	3/18/21		Shareholder	87,689
282	738	3/17/21	5/20/21	CCRO	76,037
283	714	3/15/21	3/17/21	CCRO	57,992
284	699	3/15/21	4/7/21	CCRO	34,824
285	715	3/12/21		Shareholder	33,279
286	685	3/1/21		Shareholder	49,827
287	677	2/15/21	3/15/21	Shareholder	44,239
288	1303	2/2/21	4/30/21	CAO	38,025
289	661	1/29/21	2/1/21	Shareholder	45,016
290	662	1/27/21	2/15/21	CISO	92,999
291	659	1/25/21	4/1/21	CTO	31,167
292	653	1/18/21	1/29/21	CCRO	52,403
293	651	1/11/21	5/11/21	CEO	94,095
294	639	1/4/21	2/25/21	CCRO	10,773
295	641	1/4/21	7/8/21	CCRO	54,731
296	627	12/16/20	1/31/21	CCRO	44,569
297	619	12/9/20	12/23/20	CCRO	94,442
298	623	12/7/20	6/8/21	CFO	74,298
299	622	12/7/20	3/31/21	CCRO	71,577
300	611	12/7/20	12/15/20	Shareholder	17,885
301	615	12/3/20	5/22/21	CCRO	18,404
302	607	12/3/20	1/31/21	CCRO	76,569
303	610	12/1/20	3/31/21	Shareholder	81,134
304	596	11/18/20	5/14/21	CCRO	83,606
305	599	11/16/20	6/1/21	Shareholder	14,315
306	600	11/16/20	12/17/20	CTO	58,138
307	601	11/16/20	1/29/21	COO	30,332
308	594	11/9/20	1/11/21	CCRO	64,035
309	590	11/2/20		Shareholder	79,643
310	582	10/12/20	6/9/21	COO	74,865
311	586	10/7/20		Shareholder	12,636
312	580	10/1/20	10/19/20	CAO	52,740
313	577	9/29/20	12/31/20	CCO	66,817
314	557	9/22/20	10/19/20	CCRO	86,357
315	568	9/21/20	9/22/20	CCRO	96,266
316	561	9/14/20	10/9/20	CCRO	86,782
317	534	8/18/20	2/3/21	CCRO	71,180
318	541	8/18/20	11/11/20		54,159
319	540	8/18/20	10/22/20	CCRO	53,609
320	537	8/18/20	9/17/20	CCRO	92,229
321	542	8/18/20	9/14/20	CCRO	18,289
322	475	8/3/20	3/12/21	CFO	54,814
323	521	7/27/20	9/11/20	CCRO	46,777
324	516	7/24/20	10/27/20	CCRO	32,338
325	513	7/20/20	4/30/21	CTO	84,484
326	512	7/20/20	2/26/21	CTO	76,253
327	497	7/15/20	1/8/21	COO	69,685
328	504	7/10/20	6/30/21	CEO	40,878
329	502	7/9/20		Shareholder	71,749
330	496	7/6/20	1/8/21	COO	69,627
331	501	7/6/20	10/2/20	CTO	13,707
332	498	7/2/20	11/20/20	CCRO	65,871
333	494	7/1/20	6/4/21	CTO	38,473
334	488	6/22/20	12/21/20	Shareholder	80,741
335	486	6/15/20	11/2/20	CCO	68,929
336	485	6/9/20	7/31/20	CCRO	84,883
337	482	6/8/20	7/13/20	CCRO	11,862
338	477	6/1/20	2/5/21	CCO	55,951
339	466	5/20/20	9/30/20	CCO	80,646
340	470	5/19/20	5/28/21	Shareholder	72,038
341	467	5/18/20	11/27/20	CCRO	19,249
342	448	5/4/20	6/11/21	CCO	89,412
343	443	4/27/20	1/8/21	CCRO	66,630
344	737	4/23/20	2/26/21	CISO	52,954
345	718	4/23/20	2/26/21	CISO	23,930
346	436	4/20/20	4/16/21	CCO	86,860
347	194	4/20/20	5/31/21	COO	10,979
348	706	4/20/20	2/26/21	CISO	75,164
349	432	4/16/20	6/23/20	CCRO	74,078
350	719	4/15/20	2/26/21	CISO	18,860
351	744	4/14/20	2/26/21	CISO	79,268
352	425	4/10/20	5/29/20	CCRO	45,253
353	191	4/2/20	2/8/21	CAO	46,521
354	192	4/2/20	3/23/21	CTO	80,762
355	183	3/30/20	6/30/20	CCRO	69,153
356	1485	3/18/20	8/17/20	CCO	77,977
357	1483	3/11/20	7/13/20	CD	36,069
358	1482	3/10/20	12/31/20	CCO	35,639
359	1481	3/9/20	7/2/21	Shareholder	12,413
360	172	3/5/20	7/2/21	CEO	77,031
361	1475	2/25/20	12/23/20	CTO	80,380
362	1474	2/17/20	12/31/20	CD	36,322
363	1433	2/3/20	9/30/20	CFO	27,352
364	1469	2/3/20	4/1/21	CTO	36,463
365	1467	1/27/20	6/11/20	CFO	21,549
366	1465	1/15/20	7/10/20	COO	98,055
367	1458	1/3/20	7/28/20	COO	75,081
368	1454	12/24/19	8/25/20	CCO	43,041
369	1455	12/24/19	4/2/21	CCO	13,915
370	1451	12/18/19	4/8/20	CCRO	84,530
371	1450	12/17/19	4/17/20	CFO	12,255
372	462	12/1/19	10/30/20	Shareholder	50,757
373	1420	11/25/19	2/24/21	CFO	24,916
374	1422	11/25/19	6/14/21	CCRO	79,045
375	1423	11/25/19	12/30/20	CCRO	85,320
376	1228	11/18/19	3/10/21	COO	67,290
377	1205	11/11/19	5/15/20	Shareholder	56,107
378	1429	11/1/19	7/24/20	CCO	50,602
379	1428	10/16/19	9/30/20	CCO	53,494
380	1161	10/15/19	1/6/21	CCO	28,889
381	1123	10/1/19	12/15/20	Shareholder	63,541
382	747	9/20/19	1/29/21	CISO	26,263
383	180	9/16/19	10/31/20	CEO	19,206
384	374	9/12/19	6/15/20	CTO	95,357
385	1309	9/9/19	9/30/20	CCO	72,878
386	1202	9/9/19	7/16/20	CTO	66,614
387	1363	8/26/19	8/14/20	CD	56,013
388	1163	8/12/19	10/23/20	CCO	92,664
389	1225	8/5/19	5/15/20	CTO	95,451
390	386	8/5/19	4/30/21	CCRO	54,481
391	1353	7/26/19	8/31/20	CCRO	44,273
392	1253	7/22/19	5/7/20	CCO	11,280
393	1122	7/19/19	8/28/20	CTO	38,722
394	1121	7/17/19	6/30/20	COO	20,060
395	1443	7/17/19	5/18/20	CTO	14,379
396	1156	7/15/19	10/2/20	CTO	24,996
397	1362	7/15/19	1/21/21	CCRO	26,861
398	1322	7/15/19	6/16/20	CCRO	11,386
399	1427	7/1/19	9/30/20	CCO	97,984
400	1267	7/1/19	8/11/20	COO	10,828
401	1252	7/1/19	5/7/20	CCRO	85,309
402	1154	6/18/19	3/12/21	CD	28,820
403	1401	6/13/19	10/21/20	CCRO	23,989
404	1359	6/7/19	7/31/20	CD	77,334
405	1196	5/22/19	5/14/21	CFO	42,023
406	170	5/16/19	6/11/21	CTO	41,044
407	1195	5/14/19	9/4/20	CAO	58,917
408	179	5/6/19	3/18/21	CEO	40,545
409	1193	5/6/19	8/25/20	CCRO	65,968
410	1153	5/3/19	4/20/21	Shareholder	67,701
411	1152	5/2/19	8/6/20	CCO	11,434
412	1151	4/23/19	3/1/21	CCO	96,552
413	1318	4/22/19	3/24/21	CCRO	21,466
414	1301	4/8/19	9/30/20	CCRO	15,086
415	1091	4/3/19	10/27/20	CEO	37,464
416	1348	4/1/19	4/27/20	CCRO	44,680
417	1172	3/1/19	8/26/20	CCO	15,456
418	1251	2/25/19	5/7/20	CCRO	15,390
419	178	2/15/19	7/10/20	CEO	89,943
420	380	2/14/19	4/14/21	CTO	33,940
421	1279	2/3/19	10/8/20	CCO	81,595
422	1250	1/28/19	5/7/20	CCRO	39,408
423	1440	1/9/19	6/11/21	CTO	78,296
424	1117	1/7/19	6/18/21	Shareholder	24,207
425	1249	12/24/18	5/7/20	CCRO	96,633
426	174	12/10/18	12/11/20	CTO	90,175
427	1149	12/3/18	9/30/20	CCO	72,333
428	1084	11/5/18	7/6/20	CFO	45,728
429	1086	11/1/18	12/31/20	CCO	21,860
430	1188	10/31/18	9/18/20	Shareholder	37,220
431	1221	10/29/18	2/26/21	CTO	11,085
432	1148	10/22/18	1/6/21	CCO	47,264
433	1307	10/22/18	6/26/20	CCO	69,447
434	1248	10/22/18	5/7/20	CCRO	30,531
435	1082	10/15/18	6/5/20	CD	17,932
436	1246	10/15/18	5/7/20	CCRO	74,614
437	1247	10/15/18	5/7/20	CCRO	52,416
438	1255	10/8/18	5/13/21	CCO	76,924
439	1081	10/8/18	9/30/20	CCO	79,708
440	1438	10/2/18	4/13/21	CTO	14,469
441	1080	10/1/18	12/1/20	CCRO	12,430
442	1260	10/1/18	11/11/20	CCO	46,862
443	1079	9/24/18	5/21/21	CCRO	68,017
444	1439	9/24/18	10/30/20	CTO	35,283
445	1284	9/24/18	9/30/20	CCO	59,556
446	1146	9/10/18	5/4/20	CCO	48,777
447	1290	9/5/18	9/30/20	CCO	48,405
448	1187	9/3/18	8/21/20	CTO	73,663
449	1145	8/20/18	12/28/20	CCO	66,581
450	1090	8/13/18	1/15/21	CTO	67,066
451	1147	8/13/18	7/31/20	CCO	57,971
452	1259	8/1/18	5/21/20	CCO	90,205
453	1388	7/19/18	10/13/20	CCO	13,481
454	1144	7/19/18	4/30/20	CCO	12,707
455	1075	7/16/18	4/23/21	CCO	27,113
456	1074	7/9/18	11/30/20	CCO	82,684
457	1409	6/26/18	1/8/21	CCRO	61,483
458	1237	6/20/18	5/4/20	CCO	28,584
459	1116	6/14/18	10/31/20	Shareholder	56,276
460	1245	6/12/18	5/7/20	CCO	90,391
461	1298	6/11/18	1/11/21	CAO	22,128
462	1143	6/5/18	6/30/20	CCO	64,012
463	1254	5/28/18	5/7/20	CCRO	31,334
464	1068	5/3/18	6/19/20	CAO	27,209
465	1385	5/1/18	10/12/20	CCRO	74,059
466	1425	4/24/18	2/2/21	CCO	16,530
467	1067	4/19/18	2/10/21	Shareholder	23,394
468	1100	4/16/18	1/5/21	CAO	35,731
469	1244	4/16/18	5/21/20	CCO	58,448
470	1141	4/10/18	9/30/20	CCO	60,425
471	1212	3/26/18	3/19/21	CTO	37,126
472	1258	3/26/18	5/21/20	CCO	35,273
Sheet1

 

[maxim642]

Here is the summary sheet I'd like to input the formulas to.

Master_Template(27).xlsx
	G	H	I
1	6/30/21	Division	$
2		CAO
3		CCO
4		CCRO
5		CD
6		CEO
7		CFO
8		CISO
9		COO
10		CTO
11		Shareholder
Sheet1

 

[maxim642]

Thank you for any help you can offer.

 

[Fluff]

I don't think the initial formula was summing up the salaries as intended.

Actually it was.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Employee	Hire Date	Termination Date	Division	Salary			30/06/2021	Division	$
2	833	07/07/2021		COO	71621				CAO	652274
3	841	06/07/2021		CCO	85544				CCO	3034452
4	850	06/07/2021		CCO	26653				CCRO	2986442
5	1445	16/10/2019		CTO	42325				CD	828746
6	1302	22/07/2019		CFO	13490				CEO	629783
7	1347	22/07/2019		CCRO	82806				CFO	237050
8	716	17/07/2019		CISO	25503				CISO	1771596
9	746	17/07/2019		CISO	55210				COO	565013
10	1331	11/07/2019		CCRO	90336				CTO	3142704
11	1094	08/07/2019		CTO	87078				Shareholder	974239
12	1199	08/07/2019		CAO	11657
13	1120	04/07/2019		COO	62593
14	1176	01/07/2019		CTO	48740
15	1198	01/07/2019		CFO	32965
16	1175	27/06/2019		CTO	25949
17	1276	25/06/2019		CCO	26102
18	1325	17/06/2019		CCRO	29920
19	1442	17/06/2019		CTO	10570
20	1266	17/06/2019		COO	17843
21	1224	03/06/2019		CD	57269
22	1093	27/05/2019		CTO	54144
23	1314	27/05/2019		CCRO	44244
24	1324	27/05/2019		CCRO	81952
25	159	06/05/2019		CEO	56336
26	1316	22/04/2019		CCRO	12059
27	1171	15/04/2019		Shareholder	23718
28	306	15/04/2019		CEO	60937
29	1275	12/04/2019		CCO	23033
30	1274	08/04/2019		CCO	40237
31	1312	01/04/2019		CD	81797
32	1223	18/03/2019		CD	11505
Sheet1

Cell Formulas
Range	Formula
J2:J11	J2	=SUM(FILTER($E$2:$E$500,($D$2:$D$500=I2)*($B$2:$B$500<=$H$1)*(($C$2:$C$500>=$H$1)+($C$2:$C$500=""))))

 

[maxim642]

Actually it was.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Employee	Hire Date	Termination Date	Division	Salary			30/06/2021	Division	$
2	833	07/07/2021		COO	71621				CAO	652274
3	841	06/07/2021		CCO	85544				CCO	3034452
4	850	06/07/2021		CCO	26653				CCRO	2986442
5	1445	16/10/2019		CTO	42325				CD	828746
6	1302	22/07/2019		CFO	13490				CEO	629783
7	1347	22/07/2019		CCRO	82806				CFO	237050
8	716	17/07/2019		CISO	25503				CISO	1771596
9	746	17/07/2019		CISO	55210				COO	565013
10	1331	11/07/2019		CCRO	90336				CTO	3142704
11	1094	08/07/2019		CTO	87078				Shareholder	974239
12	1199	08/07/2019		CAO	11657
13	1120	04/07/2019		COO	62593
14	1176	01/07/2019		CTO	48740
15	1198	01/07/2019		CFO	32965
16	1175	27/06/2019		CTO	25949
17	1276	25/06/2019		CCO	26102
18	1325	17/06/2019		CCRO	29920
19	1442	17/06/2019		CTO	10570
20	1266	17/06/2019		COO	17843
21	1224	03/06/2019		CD	57269
22	1093	27/05/2019		CTO	54144
23	1314	27/05/2019		CCRO	44244
24	1324	27/05/2019		CCRO	81952
25	159	06/05/2019		CEO	56336
26	1316	22/04/2019		CCRO	12059
27	1171	15/04/2019		Shareholder	23718
28	306	15/04/2019		CEO	60937
29	1275	12/04/2019		CCO	23033
30	1274	08/04/2019		CCO	40237
31	1312	01/04/2019		CD	81797
32	1223	18/03/2019		CD	11505
Sheet1

Cell Formulas
Range	Formula
J2:J11	J2	=SUM(FILTER($E$2:$E$500,($D$2:$D$500=I2)*($B$2:$B$500<=$H$1)*(($C$2:$C$500>=$H$1)+($C$2:$C$500=""))))

Excellent, thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.