Hi everyone.
I'm a project manager, developing online learning modules. I use Smartsheet, but have been unable to solve this problem within Ss, so I'm considering exporting to excel to manipulate the data there. Modules take 4 - 6 months to develop.
I have been asked to provide a total of how many modules will be in development in each month in 2021. Taking February 2021 as an example, the screenshot demonstrates that there will be 8 modules in development in February (lines 7 - 14). I need to do a similar count for every month in 2021. The formula would also need to loop in 2020 and 2022, as I have modules that start in 2020 and end in 2021, and start in 2021 and end in 2022.
I've been using this formula in Smartsheet, but it doesn't work properly.
=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) <= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2021,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)
+ COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) >= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2020,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)
+ COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) >= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2021,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2022)
I would really appreciate any help that anyone could give, I'm absolutely stuck with this.
thanks in hopeful anticipation,
I'm a project manager, developing online learning modules. I use Smartsheet, but have been unable to solve this problem within Ss, so I'm considering exporting to excel to manipulate the data there. Modules take 4 - 6 months to develop.
I have been asked to provide a total of how many modules will be in development in each month in 2021. Taking February 2021 as an example, the screenshot demonstrates that there will be 8 modules in development in February (lines 7 - 14). I need to do a similar count for every month in 2021. The formula would also need to loop in 2020 and 2022, as I have modules that start in 2020 and end in 2021, and start in 2021 and end in 2022.
I've been using this formula in Smartsheet, but it doesn't work properly.
=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) <= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2021,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)
+ COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) >= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2020,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)
+ COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) >= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2021,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2022)
I would really appreciate any help that anyone could give, I'm absolutely stuck with this.
thanks in hopeful anticipation,