Formula that cumulatively adds months based on start and end date

[dommeehan]

Hi,

First time posting on here so apologies if my request has anything missing.

I have a list of projects, each with a start and end date in one excel sheet. In another sheet I have months as the column headers, and rows containing "new projects" and "active projects". I have filled out the new projects row with the formula =COUNTIFS('Project backlog'!$C$2:$C$6,">="&Monthly!B2,'Project backlog'!$C$2:$C$6,"<="&B3) which counts the number of new projects for that month based on the project start month.

In the "active projects" row I need a formula that counts active projects based on the dates, so it will count all the new projects, plus any projects from previous months that have not concluded.

Can someone please help with the "active projects" row formula, and also if my formula for "new projects" is a long way of doing things (unsure if it is the best way or not) could someone please suggest a more efficient formula? Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.
Rather than posting images, can you please use the XL2BB add-in to post sample data to the board.
That way members can copy/paste straight to Excel, rather than having to create something from scratch.

 

[dommeehan]

Hi & welcome to MrExcel.
Rather than posting images, can you please use the XL2BB add-in to post sample data to the board.
That way members can copy/paste straight to Excel, rather than having to create something from scratch.

Thanks for the heads up. Here is the XL2BB sample:

Project Example Spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21			Project name	Project code	Start Date	End Date
2		01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020	01/10/2020	01/11/2020	01/12/2020	01/01/2021	01/02/2021	01/03/2021	01/04/2021			Cupa	CUP	03/06/2020	14/07/2020
3		31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020	31/10/2020	30/11/2020	31/12/2020	31/01/2021	28/02/2021	31/03/2021	30/04/2021			Milners	MIL	11/05/2020	18/08/2020
4																Manor	MAN	24/08/2020	30/11/2020
5	New projects	2	2	0	1	0	0	0	0	0	0	0	0			Joog	JOO	15/06/2020	28/08/2020
6	Active projects															Encad	ENC	14/05/2020	03/06/2020
Monthly

Cell Formulas
Range	Formula
C2:M2	C2	=EOMONTH(B2,0)+1
B3:M3	B3	=EOMONTH(B2,0)
B5:M5	B5	=COUNTIFS($R$2:$R$6,">="&B2,$R$2:$R$6,"<="&B3)

 

[Fluff]

Thanks for that, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020	01/10/2020	01/11/2020	01/12/2020	01/01/2021	01/02/2021	01/03/2021	01/04/2021			Project name	Project code	Start Date	End Date
2		01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020	01/10/2020	01/11/2020	01/12/2020	01/01/2021	01/02/2021	01/03/2021	01/04/2021			Cupa	CUP	03/06/2020	14/07/2020
3		31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020	31/10/2020	30/11/2020	31/12/2020	31/01/2021	28/02/2021	31/03/2021	30/04/2021			Milners	MIL	11/05/2020	18/08/2020
4																Manor	MAN	24/08/2020	30/11/2020
5	New projects	2	2	0	1	0	0	0	0	0	0	0	0			Joog	JOO	15/06/2020	28/08/2020
6	Active projects	2	4	3	3	1	1	1	0	0	0	0	0			Encad	ENC	14/05/2020	03/06/2020
7
Main

Cell Formulas
Range	Formula
C2:M2	C2	=EOMONTH(B2,0)+1
B3:M3	B3	=EOMONTH(B2,0)
B5	B5	=COUNTIFS($R$2:$R$6,">="&B$2,$R$2:$R$6,"<="&B$3)
C5:M5	C5	=COUNTIFS($R$2:$R$6,">="&C2,$R$2:$R$6,"<="&C3)
B6:M6	B6	=COUNTIFS($R$2:$R$6,"<="&B$3,$S$2:$S$6,">="&B$2)

 

[dommeehan]

Thanks for that, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020	01/10/2020	01/11/2020	01/12/2020	01/01/2021	01/02/2021	01/03/2021	01/04/2021			Project name	Project code	Start Date	End Date
2		01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020	01/10/2020	01/11/2020	01/12/2020	01/01/2021	01/02/2021	01/03/2021	01/04/2021			Cupa	CUP	03/06/2020	14/07/2020
3		31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020	31/10/2020	30/11/2020	31/12/2020	31/01/2021	28/02/2021	31/03/2021	30/04/2021			Milners	MIL	11/05/2020	18/08/2020
4																Manor	MAN	24/08/2020	30/11/2020
5	New projects	2	2	0	1	0	0	0	0	0	0	0	0			Joog	JOO	15/06/2020	28/08/2020
6	Active projects	2	4	3	3	1	1	1	0	0	0	0	0			Encad	ENC	14/05/2020	03/06/2020
7
Main

Cell Formulas
Range	Formula
C2:M2	C2	=EOMONTH(B2,0)+1
B3:M3	B3	=EOMONTH(B2,0)
B5	B5	=COUNTIFS($R$2:$R$6,">="&B$2,$R$2:$R$6,"<="&B$3)
C5:M5	C5	=COUNTIFS($R$2:$R$6,">="&C2,$R$2:$R$6,"<="&C3)
B6:M6	B6	=COUNTIFS($R$2:$R$6,"<="&B$3,$S$2:$S$6,">="&B$2)

This is brilliant, thanks very much for your help!

 

[Fluff]

You're welcome & thanks for the feedback.