Attempting to Forecast Ongoing Projects

Options
Faye Kennedy
Faye Kennedy ✭✭
edited 07/18/22 in Formulas and Functions

I have a Smartsheet with start and end dates for given projects. I am trying to create a calculation Sheet that will give me the number of ongoing projects within a specific month and year so I can forecast the number of ongoing projects we will have.

I tried several variations with the "COUNTIFS" function, but cannot seem to get it right. The closest I've come is the following, which does spit out a number. However, when I double checked it using filters on the sheet itself, it does not match :(

=COUNTIFS({Start Date}, AND(IFERROR(MONTH(@cell), 0) <= MONTH(Month@row), IFERROR(YEAR(@cell), 0) <= YEAR(Month@row)), {End Date}, AND(IFERROR(MONTH(@cell), 0) > MONTH(Month@row), IFERROR(YEAR(@cell), 0) >= YEAR(Month@row)))


From project list:

From calculations sheet:



Answers

  • Genevieve P.
    Options

    Hi @Faye Kennedy

    It looks like you may just be missing an = sign!

    You're currently looking for if the Start Date is less than or equal to the month and year of your date, and if the End Date month is greater than the month (but not equal to) and the Year is equal to or greater than.

    This means your current number would have been excluding any of your rows that ended in the same month as your Month cell.

    Try adding in the = where I've bolded it and see if this now gives you the correct number:

    =COUNTIFS({Start Date}, AND(IFERROR(MONTH(@cell), 0) <= MONTH(Month@row), IFERROR(YEAR(@cell), 0) <= YEAR(Month@row)), {End Date}, AND(IFERROR(MONTH(@cell), 0) >= MONTH(Month@row), IFERROR(YEAR(@cell), 0) >= YEAR(Month@row)))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!