Count Projects happening within the same month

Hello! I am trying to count projects by person overtime (monthly) in order to show how many projects a person on my team has going on simultaneously.

I am using two date columns- Kick off date (KO) and implementation date (Imp Date) with this formula. So, I want to count everything that is happening in the month of march (for example). Some projects kick off in march, some end in march and some continue on through march and I want to catch them all.

=COUNTIFS({AM}, CONTAINS("person", @cell), {KO}, IFERROR(YEAR(@cell), 0) < 2020, {KO}, IFERROR(MONTH(@cell), 0) >= 6) + COUNTIFS({AM}, CONTAINS("Dennis Klotter", @cell), {KO}, IFERROR(MONTH(@cell), 0) <= 6, {Imp. Date}, IFERROR(MONTH(@cell), 0) >= 6)

When I check the formula to count from gantt view, the number is off. I think it is because some projects started the previous year. I'd love to create this formula so it holds up year over year and doesn't need manual changes to get data.

Thank you!

Best Answer


  • Thanks for your help Stefan!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!