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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!