Formula that cumulatively adds months based on start and end date

dommeehan

New Member
Joined
Aug 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Monthly.pngProjetcs.png
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRS
1May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21Project nameProject codeStart DateEnd Date
201/05/202001/06/202001/07/202001/08/202001/09/202001/10/202001/11/202001/12/202001/01/202101/02/202101/03/202101/04/2021CupaCUP03/06/202014/07/2020
331/05/202030/06/202031/07/202031/08/202030/09/202031/10/202030/11/202031/12/202031/01/202128/02/202131/03/202130/04/2021MilnersMIL11/05/202018/08/2020
4ManorMAN24/08/202030/11/2020
5New projects220100000000JoogJOO15/06/202028/08/2020
6Active projectsEncadENC14/05/202003/06/2020
Monthly
Cell Formulas
RangeFormula
C2:M2C2=EOMONTH(B2,0)+1
B3:M3B3=EOMONTH(B2,0)
B5:M5B5=COUNTIFS($R$2:$R$6,">="&B2,$R$2:$R$6,"<="&B3)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff New.xlsm
ABCDEFGHIJKLMNOPQRS
101/05/202001/06/202001/07/202001/08/202001/09/202001/10/202001/11/202001/12/202001/01/202101/02/202101/03/202101/04/2021Project nameProject codeStart DateEnd Date
201/05/202001/06/202001/07/202001/08/202001/09/202001/10/202001/11/202001/12/202001/01/202101/02/202101/03/202101/04/2021CupaCUP03/06/202014/07/2020
331/05/202030/06/202031/07/202031/08/202030/09/202031/10/202030/11/202031/12/202031/01/202128/02/202131/03/202130/04/2021MilnersMIL11/05/202018/08/2020
4ManorMAN24/08/202030/11/2020
5New projects220100000000JoogJOO15/06/202028/08/2020
6Active projects243311100000EncadENC14/05/202003/06/2020
7
Main
Cell Formulas
RangeFormula
C2:M2C2=EOMONTH(B2,0)+1
B3:M3B3=EOMONTH(B2,0)
B5B5=COUNTIFS($R$2:$R$6,">="&B$2,$R$2:$R$6,"<="&B$3)
C5:M5C5=COUNTIFS($R$2:$R$6,">="&C2,$R$2:$R$6,"<="&C3)
B6:M6B6=COUNTIFS($R$2:$R$6,"<="&B$3,$S$2:$S$6,">="&B$2)
 

dommeehan

New Member
Joined
Aug 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff New.xlsm
ABCDEFGHIJKLMNOPQRS
101/05/202001/06/202001/07/202001/08/202001/09/202001/10/202001/11/202001/12/202001/01/202101/02/202101/03/202101/04/2021Project nameProject codeStart DateEnd Date
201/05/202001/06/202001/07/202001/08/202001/09/202001/10/202001/11/202001/12/202001/01/202101/02/202101/03/202101/04/2021CupaCUP03/06/202014/07/2020
331/05/202030/06/202031/07/202031/08/202030/09/202031/10/202030/11/202031/12/202031/01/202128/02/202131/03/202130/04/2021MilnersMIL11/05/202018/08/2020
4ManorMAN24/08/202030/11/2020
5New projects220100000000JoogJOO15/06/202028/08/2020
6Active projects243311100000EncadENC14/05/202003/06/2020
7
Main
Cell Formulas
RangeFormula
C2:M2C2=EOMONTH(B2,0)+1
B3:M3B3=EOMONTH(B2,0)
B5B5=COUNTIFS($R$2:$R$6,">="&B$2,$R$2:$R$6,"<="&B$3)
C5:M5C5=COUNTIFS($R$2:$R$6,">="&C2,$R$2:$R$6,"<="&C3)
B6:M6B6=COUNTIFS($R$2:$R$6,"<="&B$3,$S$2:$S$6,">="&B$2)


This is brilliant, thanks very much for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,186
Messages
5,570,744
Members
412,338
Latest member
ebf6543
Top