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

Jeffrey_PMO ✭✭✭✭
edited 02/04/22 in Formulas and Functions

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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!