Using COUNTIFS to summarize project statuses on the 17th of each month but data wipes each new month

Jeffrey_PMO

Hello All,

I'm using an Automated Workflow to pull in 40+ project status (RYGB) on the third Monday of each month for Scope, Schedule and Budget. I'm then using another sheet to summarize that information. The formula works (shown below) but once we transition into a new month the data reverts to 0 until that new months third Monday occurs. For example, in the second screenshot below when we go from January into February the data changes from 2 to 0.

Is it possible for Smartsheet to keep in the past months data until the third Monday of the new month occurs? Using the example above, keep in Januarys data (2) until February's third Monday (2/21).

@Paul Newcome I'm using the formula you provided back in 2020 (HERE) but I just cant seem to find where to tweak it.

=COUNTIFS({AMS - Active Project Statuses 3rd Monday - Created}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {AMS - Active 3rd Monday - Overall 1}, "Yellow")

Thank you!

Paul Newcome

You are going to want a second COUNTIFS that counts for the previous month and then use an IF statement to say that

=IF(DAY(TODAY())< 17, COUNTIFS(previous month), COUNTIFS(current month))

Jeffrey_PMO

Would this portion =IF(DAY(TODAY())< 17, COUNTIFS(previous month), COUNTIFS(current month)) be inserted into the original formula (below)?

=COUNTIFS({AMS - Active Project Statuses 3rd Monday - Created}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {AMS - Active 3rd Monday - Overall 1}, "Yellow")

Paul Newcome

No. You would need to fill in each of the two COUNTIFS functions. One for before the 17th and one for after the 17th.