# Determine how many projects are active during given period

Options
✭✭✭✭✭✭

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)

Tags:

• ✭✭✭✭✭✭
Options

You are going to want to compare start and end date. Start date being less than the high range and end date being greater than the low range.

=COUNTIFS({Start Date}, @cell <= DATE(2021, 04, 30), {End Date}, @cell >= DATE(2021, 04, 01))

The above will tell you how many projects are active in April 2021.

• ✭✭✭✭✭✭
Options

You are going to want to compare start and end date. Start date being less than the high range and end date being greater than the low range.

=COUNTIFS({Start Date}, @cell <= DATE(2021, 04, 30), {End Date}, @cell >= DATE(2021, 04, 01))

The above will tell you how many projects are active in April 2021.

• ✭✭✭✭✭✭
Options

thanks a lot, everything works perfect, as usual!

You're awesome! :)

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!