Sequencial numbering based on Criteria


Can you please help with the following formula to work in Smartsheet:


I am creating a numbering system that are criteria based but then want them to be sequential based on the selected criteria:

See Attached.

The last 3 digit should be sequential based on the initial 3 digits which are criteria based (Department & Type). I am able to establish the first part, but are having difficulty getting the sheet to generate the -000 part.

The formula above works great in excel, not to get this to work in smartsheet is the problem.

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 your Doc #:

    =formula_to_generate_first_portion + "-" + RIGHT("00" + COUNTIFS(Department:Department, @cell = Department@row, [Document Type]:[Document Type], @cell = [Document Type]@row, Auto:Auto, @cell<= Auto@row), 3)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!