Can I have a column calculate and store a counter and restart for a new group?

Is it possible to have a column calculate a counter that restarts when another column's value changes?

What I mean, imagine 3 columns:
* Event name
* Topic name
* Feature number

I want to make Feature number a counter. And I want the counter to restart every time the Event name and Topic name changes. I have a feeling it can't be done with a formula, and I'm looking at the automation to update the Feature number cell whenever a row is added. But I haven't figured out yet the formula to calculate the max(Feature number) for the combination of Event name and Topic name from the current row. and then add 1 to it

I tried setting up an automation to update Feature number when a new row is added and the value for the Feature number
=MAX(COLLECT([Feature Number], [Event Name], [Event Name]@row, [Topic Name], [Topic Name]@row))+1

but my Feature Number is blank after I save the sheet. (I manually had entered 1 for the first Pairing number thinking that for a new sheet the max would be Null.

Find more posts tagged with

Formulas

Accepted answers

All comments

Andrée Starå

https://community.smartsheet.com/discussion/141507/can-i-have-a-column-calculate-and-store-a-counter-and-restart-for-a-new-group

Hi,

I hope you're well and safe!

Yes, and there are multiple ways. The best method depends on your specific needs.

1️⃣ Workflow that changes from 1,2,3, and so on, when you don't have too many counts.

2️⃣ Helper sheet that collects the row when what you'd like to count changes, and then you'd use cross-sheet formulas to count it.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

CLH

I think what you're describing with the workflow is what I'm trying to do with automation, right? But the formula I built isn't storing any results in the cell.

Andrée Starå

https://community.smartsheet.com/discussion/comment/491509#Comment_491509

You'd use the Change Cell action in a condition path structure.

Is that what you're doing?

Please share some screenshots and/or additional information. (Delete/replace any confidential/sensitive information before sharing.) That would make it easier to help.

✅ Remember! Did I help answer your questions or solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

CLH

I had another project steal my time last week, and just got back to this one.

I'm sorry; I don't understand the steps behind what you're describing.
Here's what I've got.

My primary key is just a formula that appends event name and topic name. I want to number the features within event. I'm looking for a formula/process that will number the first two rows 1 and 2 and then number row 3 as 1 again, because it's the first topic for a new event.

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, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.