Counting Project status within given time

[optimushunk]

Hey guys,

I am trying to develop a model which can give me the count of projects on a monthly basis considering the status / stage that they are in.

Sample Data:

		Definition	Definition	ITT & Approvals	ITT & Approvals	Execution	Execution
Project No.	Project Name	Start Date	End Date	Start Date	End Date	Start Date	End Date
20C001C	Project 1	17/02/2020​	29/05/2020​	01/06/2020​	17/07/2020​	31/07/2020​	08/10/2020​
20C002C	Project 2	17/02/2020​	10/07/2020​	13/07/2020​	28/08/2020​	14/09/2020​	20/11/2020​
20C003C	Project 3	20/07/2020​	11/09/2020​	14/09/2020​	23/10/2020​	05/10/2020​	27/11/2020​
20C004C	Project 4	02/03/2020​	19/06/2020​	22/06/2020​	31/07/2020​	12/08/2019​	30/10/2020​
20C005C	Project 5	14/04/2020​	27/05/2020​	28/05/2020​	01/07/2020​	02/07/2020​	10/09/2020​
20C006C	Project 6	06/05/2020​	17/06/2020​	18/06/2020​	22/07/2020​	23/07/2020​	01/10/2020​
20C007C	Project 7	28/05/2020​	08/07/2020​	09/07/2020​	12/08/2020​	13/08/2020​	22/10/2020​
20C008C	Project 8	18/06/2020​	29/07/2020​	30/07/2020​	03/09/2020​	04/09/2020​	12/11/2020​
20C009C	Project 9	02/11/2020​	11/12/2020​	14/12/2020​	25/01/2021​	26/01/2021​	07/04/2021​
20C010C	Project 10	20/07/2020​	28/08/2020​	01/09/2020​	05/10/2020​	06/10/2020​	14/12/2020​

Sample Output
	Not started	Definition	ITT & Approvals	Execution	Complete
31/01/2018​	1	5	3	1
28/02/2018​	4	2	3	1
31/03/2018​
30/04/2018​
31/05/2018​
30/06/2018​
31/07/2018​
31/08/2018​
30/09/2018​
31/10/2018​
30/11/2018​
31/12/2018​

Count active project within the defined month start date to end date.

If Definition date is not reached, the project has not started.

If Execution date has passed, the project is completed. If blank, the project is in execution

The words 'Definition', 'ITT & Approvals' and 'Execution' are the titles within an MS Project file. The data is first exported into excel to arrange data as shown above.

Any suggestions on how to achieve this?

 

[optimushunk]

Below is a more accurate output. I made up the numbers previously...which doesn't really help testing. Sorry about that.

Expected Output
	Not started	Definition	ITT & Approvals	Execution	Complete
31/01/2020​	10​	0​	0​	0​	0​
29/02/2020​	8​	2​	0​	0​	0​
31/03/2020​	7​	3​	0​	0​	0​
30/04/2020​	6​	4​	0​	0​	0​
31/05/2020​
30/06/2020​
31/07/2020​
31/08/2020​
30/09/2020​
31/10/2020​
30/11/2020​
31/12/2020​	0​	0​	1​	2​	7​

 

[optimushunk]

Here is where I have got with some help from elsewhere

In B20 copied down:

=COUNTIF(C$3:C$12,">"&$A20)

In C20 copied across to E20 and down:

=COUNTIF(D$3:D$12,">"&$A20)-SUM($B20:B20)

In F20 copied down:

=COUNTIF(H$3:H$12,"<="&$A20)

The results are as follows. Getting there ...

	Not started	Definition	ITT & Approvals	Execution	Complete	Total
31/01/2020​	10​	0​	0​	0​	0​	10​
29/02/2020​	8​	2​	0​	0​	0​	10​
31/03/2020​	7​	3​	0​	0​	0​	10​
30/04/2020​	6​	4​	0​	0​	0​	10​
31/05/2020​	4​	4​	1​	1​	0​	10​
30/06/2020​	3​	3​	0​	4​	0​	10​
31/07/2020​	1​	2​	0​	3​	0​	6​
31/08/2020​	1​	1​	1​	1​	0​	4​
30/09/2020​	1​	0​	0​	2​	1​	4​
31/10/2020​	1​	0​	0​	0​	5​	6​
30/11/2020​	0​	1​	0​	0​	8​	9​
31/12/2020​	0​	0​	0​	1​	8​	9​

The total should add up to 10 as there are 10 projects being analysed in the sample.