Counting the Number of Employees at Start and End of Month

[Jehovahthicness]

Hi Excel forum, I need help counting the number of employees at the start and end of each month. I have the Start Date and End Date in the attached sheet. I would need to fill out the information correctly on the second tab of the sheet, but I can't quite get the calculations correct.

Second Tab format:

 

[Peter_SSs]

Welcome to the MrExcel board!

This would likely attract more potential helpers as it would save a lot of manual typing to test.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bebo021999]

Book1
	A	B	C
1	Title	Start date	End date
2	A	06/01/2018	07/12/2019
3	B	06/05/2018	05/04/2020
4	C	15/07/2019	14/06/2021
5	D	27/01/2020	27/12/2021
6	E	26/05/2020	26/04/2022
7	F	24/06/2020	25/05/2022
8	G	15/08/2015	15/07/2017
9	H	06/06/2019	06/05/2021
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=B2+700

Book1
	A	B	C	D
1	2021		Starting	End
2		January	5	5
3		February	5	5
4		March	5	5
5		April	5	5
6		May	5	4
7		Jun	4	3
8		July	3	3
9		August	3	3
10		September	3	3
11		October	3	3
12		November	3	3
13		December	3	2
14	2022		Starting	End
15		January	2	2
16		February	2	2
17		March	2	2
18		April	2	1
19		May	1	0
20		Jun	0	0
21		July	0	0
22		August	0	0
23		September	0	0
24		October	0	0
25		November	0	0
26		December	0	0
Sheet2

Cell Formulas
Range	Formula
C15:C26,C2:C13	C2	=COUNTIFS(Sheet1!$B$2:$B$9,"<=" & DATE(LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1),MONTH(DATEVALUE(1&$B2)),1),Sheet1!$C$2:$C$9,">=" &DATE(LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1),MONTH(DATEVALUE(1&$B2)),1))
D15:D26,D2:D13	D2	=COUNTIFS(Sheet1!$B$2:$B$9,"<=" & EOMONTH(DATE(LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1),MONTH(DATEVALUE(1&$B2)),1),0),Sheet1!$C$2:$C$9,">=" &EOMONTH(DATE(LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1),MONTH(DATEVALUE(1&$B2)),1),0))