Attempting to Forecast Ongoing Projects

Faye Kennedy

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:

Genevieve P.

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