Counting Salary within certain dates, taking into account employment status

[Cjhicks2708]

Hi, I am looking to calculate total salary month on month, but need to also consider if the person is still employed. What i am looking to do is present a month on month total spent on salaries. I have the hire date and the employment status. I am not sure if i should be using a countif here?

Book1
	A	B	C	D	E	F	G	H	I
1	Employment Status	Hire Date	Probabtion Date	End date	Pay rate	Base Currency	Paid per	Pay type	Salary £
2	Terminated	24/02/2020	23/07/2020	27/11/2020	£ 43,400.00	GBP	Year	Salary	£ 43,400.00
3	Full-Time	14/11/2019	12/04/2020		£ 52,500.00	GBP	Year	Salary	£ 52,500.00
4	Full-Time	07/09/2020	04/02/2021		£ 49,000.00	GBP	Year	Salary	£ 49,000.00
5	Terminated	29/10/2018	28/03/2019	10/01/2020	$ 75,478.90	USD	Year	Salary	£ 54,344.81
6	Terminated	01/07/2019	28/11/2019	08/07/2020	£ 63,000.00	GBP	Year	Salary	£ 63,000.00
7	Terminated	06/01/2020	04/06/2020	06/10/2020	£ 35,000.00	GBP	Year	Salary	£ 35,000.00
8	Terminated	02/08/2017	30/12/2017		$ 61,600.00	USD	Year	Salary	£ 44,352.00
9	Full-Time	29/04/2019	26/09/2019		£ 28,000.00	GBP	Year	Salary	£ 28,000.00
10	Full-Time	11/09/2017	08/02/2018		$ 120,615.60	USD	Year	Salary	£ 86,843.23
11	Terminated	30/03/2020	27/08/2020	30/04/2021	£ 25,900.00	GBP	Year	Salary	£ 25,900.00
12	Full-Time	10/06/2019	07/11/2019		£ 38,500.00	GBP	Year	Salary	£ 38,500.00
13	Terminated	24/07/2017	21/12/2017	13/01/2020	$ 105,538.30	USD	Year	Salary	£ 75,987.58
14	Terminated	02/12/2019	30/04/2020	15/07/2020	£ 42,000.00	GBP	Year	Salary	£ 42,000.00
15	Full-Time	18/02/2019	18/07/2019		£ 37,100.00	GBP	Year	Salary	£ 37,100.00
16	Full-Time	21/09/2020	18/02/2021		£ 21,000.00	GBP	Year	Salary	£ 21,000.00
17	Full-Time	28/09/2020	25/02/2021		£ 70,000.00	GBP	Year	Salary	£ 70,000.00
18	Full-Time	25/11/2019	23/04/2020		£ 47,600.00	GBP	Year	Salary	£ 47,600.00
19	Full-Time	13/01/2020	11/06/2020		£ 21,000.00	GBP	Year	Salary	£ 21,000.00
20	Terminated	27/04/2020	24/09/2020	31/03/2021	£ 33,600.00	GBP	Year	Salary	£ 33,600.00
21	Full-Time	15/07/2019	12/12/2019		£ 24,500.00	GBP	Year	Salary	£ 24,500.00
22	Full-Time	28/09/2020	25/02/2021		£ 59,500.00	GBP	Year	Salary	£ 59,500.00
23	Full-Time	13/01/2020	11/06/2020		€ 88,200.00	EUR	Year	Salary	£ 74,970.00
24	Terminated	16/09/2019	13/02/2020	14/08/2020	£ 53,900.00	GBP	Year	Salary	£ 53,900.00
25	Terminated	24/06/2019	21/11/2019	31/12/2019	£ 31,500.00	GBP	Year	Salary	£ 31,500.00
26	Full-Time	01/03/2019	29/07/2019		£ 37,056.25	GBP	Year	Salary	£ 37,056.25
Sheet1

 

[steve the fish]

You could do it with a couple of SUMIFS formulas. Put a date in K2 then use:

=SUMIFS(I:I,D:D,"",B:B,"<="&K2)/12+SUMIFS(I:I,D:D,">="&K2,B:B,"<="&K2)/12

You can then add additional dates into K3, K4 etc and drag down.

Or maybe a sumproduct:

=SUMPRODUCT(($I$2:$I$100/12),--($B$2:$B$100<=K2),--(($D$2:$D$100>=K2)+($D$2:$D$100="")))

 

[Crystalyzer]

I first placed the value 1/1/1999 in cell J1
then I placed the formula below into cells K1 thru U1

=DATE(YEAR(J1),MONTH(J1)+1,1)

Then I entered the formula below in cells J2 thru U26

=IF($B2<=J$1,IF(OR(ISBLANK($D2),$D2>=J$1),$I2/12,0),0)

then I summed columns J thru U to get the monthly salary for each month

You can change the value in cell J1 to begin any year to check the monthly results for that year.

Book1 (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Employment Status	Hire Date	Probabtion Date	End date	Pay rate	Base Currency	Paid per	Pay type	Salary £	01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021	Total for Year
2	Terminated	24/02/2020	23/07/2020	27/11/2020	43400	GBP	Year	Salary	43400	-	-	-	-	-	-	-	-	-	-	-	-	-
3	Full-Time	14/11/2019	12/04/2020		52500	GBP	Year	Salary	52500	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	4,375.00	52,500.00
4	Full-Time	07/09/2020	04/02/2021		49000	GBP	Year	Salary	49000	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	4,083.33	49,000.00
5	Terminated	29/10/2018	28/03/2019	10/01/2020	75478.9	USD	Year	Salary	54344.808	-	-	-	-	-	-	-	-	-	-	-	-	-
6	Terminated	01/07/2019	28/11/2019	08/07/2020	63000	GBP	Year	Salary	63000	-	-	-	-	-	-	-	-	-	-	-	-	-
7	Terminated	06/01/2020	04/06/2020	06/10/2020	35000	GBP	Year	Salary	35000	-	-	-	-	-	-	-	-	-	-	-	-	-
8	Terminated	02/08/2017	30/12/2017		61600	USD	Year	Salary	44352	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	3,696.00	44,352.00
9	Full-Time	29/04/2019	26/09/2019		28000	GBP	Year	Salary	28000	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	2,333.33	28,000.00
10	Full-Time	11/09/2017	08/02/2018		120615.6	USD	Year	Salary	86843.232	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	7,236.94	86,843.23
11	Terminated	30/03/2020	27/08/2020	30/04/2021	25900	GBP	Year	Salary	25900	2,158.33	2,158.33	2,158.33	2,158.33	-	-	-	-	-	-	-	-	8,633.33
12	Full-Time	10/06/2019	07/11/2019		38500	GBP	Year	Salary	38500	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	3,208.33	38,500.00
13	Terminated	24/07/2017	21/12/2017	13/01/2020	105538.3	USD	Year	Salary	75987.576	-	-	-	-	-	-	-	-	-	-	-	-	-
14	Terminated	02/12/2019	30/04/2020	15/07/2020	42000	GBP	Year	Salary	42000	-	-	-	-	-	-	-	-	-	-	-	-	-
15	Full-Time	18/02/2019	18/07/2019		37100	GBP	Year	Salary	37100	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	3,091.67	37,100.00
16	Full-Time	21/09/2020	18/02/2021		21000	GBP	Year	Salary	21000	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	21,000.00
17	Full-Time	28/09/2020	25/02/2021		70000	GBP	Year	Salary	70000	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	5,833.33	70,000.00
18	Full-Time	25/11/2019	23/04/2020		47600	GBP	Year	Salary	47600	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	3,966.67	47,600.00
19	Full-Time	13/01/2020	11/06/2020		21000	GBP	Year	Salary	21000	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	1,750.00	21,000.00
20	Terminated	27/04/2020	24/09/2020	31/03/2021	33600	GBP	Year	Salary	33600	2,800.00	2,800.00	2,800.00	-	-	-	-	-	-	-	-	-	8,400.00
21	Full-Time	15/07/2019	12/12/2019		24500	GBP	Year	Salary	24500	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	2,041.67	24,500.00
22	Full-Time	28/09/2020	25/02/2021		59500	GBP	Year	Salary	59500	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	4,958.33	59,500.00
23	Full-Time	13/01/2020	11/06/2020		88200	EUR	Year	Salary	74970	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	6,247.50	74,970.00
24	Terminated	16/09/2019	13/02/2020	14/08/2020	53900	GBP	Year	Salary	53900	-	-	-	-	-	-	-	-	-	-	-	-	-
25	Terminated	24/06/2019	21/11/2019	31/12/2019	31500	GBP	Year	Salary	31500	-	-	-	-	-	-	-	-	-	-	-	-	-
26	Full-Time	01/03/2019	29/07/2019		37056.25	GBP	Year	Salary	37056.25	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	3,088.02	37,056.25
27										62,618.46	62,618.46	62,618.46	59,818.46	57,660.12	57,660.12	57,660.12	57,660.12	57,660.12	57,660.12	57,660.12	57,660.12	708,954.82
Sheet3

Cell Formulas
Range	Formula
K1:U1	K1	=DATE(YEAR(J1),MONTH(J1)+1,1)
J2:U26	J2	=IF($B2<=J$1,IF(OR(ISBLANK($D2),$D2>=J$1),$I2/12,0),0)
V2:V27	V2	=SUM(J2:U2)
J27:U27	J27	=SUM(J2:J26)