How to count the latest status on a record

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

Find more posts tagged with

Formulas

Accepted answers

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.