Incrementing a Number by One for Each Time a Status is Selected

I have a sheet where we have a variety of values in a Status column. I am looking to have a separate column that would count how many times a row has gone through a particular status (" Revise") so we can see how many rounds of revisions a particular project goes through. We were going to call it "# of Revisions". The first time this status is selected, it should fill in a "1". When it moves to another status, the "1" should remain. If it moves back to "Implement Revisions" status, the column would change to "2". If a row has never gone through the "Revise" status, the column would be either blank or show a "0" value.

I am not sure whether this would be handled via an automation or a formula, or a combination of both.

Any suggestions?

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

Andrée Starå

Hi @RyanFMurphy

I hope you're well and safe!

The easiest way would be something like this:

Add a so-called Helper sheet
Add a Copy Row Workflow that would trigger each time the Status changes to Revise (or other value)
Use a COUNTIFS cross-sheet formula to count how many rows with Revise are in the other sheet
If you have something unique already for each row, you will use that to identify the correct rows in the helper sheet. Otherwise, you could add an Auto Number column to have it.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

RyanFMurphy

Thanks for the suggestion. However, I think I am running into a "nuance" related to how Smartsheet handles links within cells. For the primary column field, we're using it to designate a project number from another system. In some/many of those project numbers, we're manually inserting a link so it can be clicked to go into that system to work on the project. The primary column field is the only column that has a unique identifier.

For some reason, when I have the COUNTIFS reference the the primary column with the project numbers and the links behind the values, the formula displays a 0 result. If I use another column in the reference sheet (which are not unique), it simply counts how many times that value has shown up on the helper sheet (which is incorrect).

Formula entered is:

=COUNTIFS({[Primary Column from Helper Sheet]:[Primary Column from Helper Sheet]}, [Primary Column]@row)

Is it possible that the COUNTIFS doesn't work when there's a link behind content in the range/criteria?

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.