Counting projects on monthly basis considering project stages

Hi,

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.

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


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


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?

Edit1: Changed 2018 to 2020 for the output. Sorry, I am unable to attach the excel into this post for some reason. So, I've posted the data as it is. Let me know if there is a better way to share the excel sample sheet I've prepared.
Edit2: Excel attached! Thanks AliGW

Welcome to the forum.

The instructions about uploading sample workbooks are at the top of the page.

No - it just doesn't work.

You forgot expected outcomes. Please manually complete a few rows of data and reattach the workbook.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
18	Expected Output
19		Not started	Definition	ITT & Approvals	Execution	Complete
20	31/01/2020
21	29/02/2020
22	31/03/2020
23	30/04/2020
24	31/05/2020
25	30/06/2020
26	31/07/2020
27	31/08/2020
28	30/09/2020
29	31/10/2020
30	30/11/2020
31	31/12/2020

Sheet: Sheet1

In C20 copied down:

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

In D20 copied down:

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

and so on ...

Yes, first one for B20 and so on - sorry.

As for the rest, I wasn't clear on the requirement. I have run out of time today - sorry.

Maybe this will get you a bit closer. 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)

It would help (me) if you filled in the entire grid manually so that I know what we are aiming for.