How do I build a formula to count up a specific value based on another column

RobMartinez

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

Paul Newcome

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)

Paul Newcome

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)

RobMartinez

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?

Paul Newcome

Did you create the auto number column and reference it in the formula?

RobMartinez

https://community.smartsheet.com/discussion/comment/406297#Comment_406297

Thank you, that solved it!

Amazing!

Paul Newcome

Happy to help. 👍️