Determine how many projects are active during given period

Hi all,

I was wondering if anyone can help? I've got quite a number of projects running from Timepoint A to Timepoint B (several months) and wondered if it was possible to write a formula in my (separate or the same sheet) metrics sheet to determine how many projects are active during given month?

For instance, I have 5 projects starting in March 2021 and another 14 starting in April 2021. I'd like to know how many total would be active if each project runs for exactly 2 months? Id est, if I have 5 projects starting in March and another 14 in April, I know that 19 projects are active/running in April, 19 projects are active in May and 14 would be active in June). This is my layout:


The current formula I have is only for counts of projects starting in a given month but not telling me how many are active in total (starting or in progess) next month:

=COUNTIFS({Ref 1}, IFERROR(YEAR(@cell), 0) = 2021, {Ref 1}, IFERROR(MONTH(@cell), 0) = 1)


Thanks in advance.

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!