How to count the latest status on a record

egrillasca

Hello!

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

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)