How to count the latest status on a record


I have a sheet that's feeding on an automation from another sheet and it is set to copy a row whenever a status changes on a case. Now I'm working on a series of monthly dashboards and I want to show the count of cases that where in each one of the status before each month ended.

This is my sheet that's feeding from the automation. I added the modified column so I can use this as a condition, (date range per every month) but I cant figure out the formula.

=count(distinct(collect({case id}, {qc general status},"Not Ready for Inspection", {modified}, ???)))

the logic of the criterion for the modified column should be: the greatest date less or equal to the last day of the month??

This is what I want to fill out



  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    I'm not entirely sure what your end goal is, but it at least sounds like you need a way to mark the latest date of a month? Below I built a helper column that maybe you could implement into your formula to get what you want. It checks off the latest day of a given month/year combo.

    =IF(MAX(COLLECT(Modified:Modified, Modified:Modified, ISDATE(@cell), Modified:Modified, MONTH(@cell) = MONTH(Modified@row), Modified:Modified, YEAR(@cell) = YEAR(Modified@row))) = Modified@row, 1, 0)

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!