# Sequencial numbering based on Criteria

Options
✭✭✭

=TEXT(VLOOKUP(C3,LIST!\$A\$2:\$B\$12,2,0),VLOOKUP(C3,LIST!\$A\$2:\$B\$12,2,0)&"0")&TEXT(VLOOKUP(D3,LIST!\$D\$2:\$E\$17,2,0),"00-")&TEXT(COUNTIF(\$C\$1:C2,C3)+1,"000")

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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭
Options

Paul, Thanks - this worked great.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!