How do I build a formula to count up a specific value based on another column
Trying to build a reference ID that is based on another column's content while counting up the reference ID based on the amount of that specific value from another column.
Multiple incidents can occur against the same customer PO and we need a reference number that starts at 01 and counts up if customer PO comes up again during other captured incidents.
Your help is much appreciated!!
Best
Rob
Best Answer
-
You are going to need an Auto-Number column (called "Auto" in this example) that has no special formatting. Then you would use this formula to generate the Ref#.
=[Customer PO]@row + "-" + RIGHT("0" + COUNTIFS([Customer PO]:[Customer PO], @cell = [Customer PO]@row, Auto:Auto, @cell<= Auto@row), 2)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You are going to need an Auto-Number column (called "Auto" in this example) that has no special formatting. Then you would use this formula to generate the Ref#.
=[Customer PO]@row + "-" + RIGHT("0" + COUNTIFS([Customer PO]:[Customer PO], @cell = [Customer PO]@row, Auto:Auto, @cell<= Auto@row), 2)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks so much for the response! I really appreciate the support!
It does count up for all REF numbers but that eliminates them being unique. We would need to count it up from 01-09 in this case but it looks like below. Is there a way to fix that?
-
Did you create the auto number column and reference it in the formula?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, that solved it!
Amazing!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!