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

RobMartinez · 11/21/23

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 · 11/22/23

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 · 11/22/23

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 · 11/22/23

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 · 11/22/23

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

RobMartinez · 11/22/23

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

Thank you, that solved it!

Amazing!

Paul Newcome · 11/27/23

Happy to help. 👍️

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

Best Answer

Answers

Help Article Resources

Categories