# Sequential Number

Options
✭✭✭

I am creating a Project Numbering system in a sheet that are generated based on selected criteria's. The last 3 digits would need to be sequential numbers "000"

Auto-numbering will not work for this application as the criteria's differ for each program.

Follow is what the formula looks like in excel:

&TEXT(COUNTIF(\$B\$2:B3,B4)+1,"000")

An example of how the number looks at the end is:

Tags:

• ✭✭✭✭✭✭
Options

Try something along the lines of

="C539-50-G" + IF(COUNTIFS(\$B\$2:B3, B4) < 10, "00", IF(COUNTIFS(\$B\$2:B3, B4) < 100, "0")) + COUNTIFS(\$B\$2:B3, B4)

• ✭✭✭
Options

great Thx, will try this.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭
Options

Hi Paul,

I have similar request to create a auto number, sequence is

"ECR- 2021-8-0001": ECR- Year- Month- 0000

I am able to pull the month from the date it the log was created, but not able to update the last batch.

If its September: ECR-2021-09-0001

I use "ECR-2021" + "-" + MONTH(Created1) formula , but need to input sequence numbering

eg: ECR-2021-8-0001

ECR-2021-8-0002

ECR-2021-9-0001

ECR-2021-9-0002

Can you suggest better way to fill it up?

• ✭✭
Options

This is what i tried but keep getting same value for the month. Is there any better solution?

• ✭✭✭✭✭✭
Options

@APalani Try something like this...

="ECR-2021" + "-" + MONTH(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10, "000", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 100, "00", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 1000, "0"))) + COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))

• ✭✭
Options

Paul, thanks alot , it works really good. I tried on a log with same day/time, it generated different ECR number.

But I was asked to include the date as well. I want to know if its feasible.

ECR-Year-Month-Date00

In above format Thanks!

• ✭✭✭✭✭✭
Options

To include the day it would be this:

="ECR-2021" + "-" + MONTH(Created@row) + "" + DAY(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10, "000", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 100, "00", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 1000, "0"))) + COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))

• ✭✭
Options

Thank you so much Paul, the function works well.

• ✭✭
Options

Paul, one request: is it possible to increment based on the date

For eg:

ECR-2021-08-23-0001

ECR-2021-08-24-0001

ECR-2021-08-24-0002

something like this.

• ✭✭✭✭✭✭
Options

Yes. This should work for you...

="ECR-" + YEAR(Created@row) + "-" + IF(MONTH(Created@row) < 10, "0") + MONTH(Created@row) + "-" + IF(DAY(Created@row) < 10, "0") + DAY(Created@row) + "-" + IF(COUNTIFS(Created:Created, @cell = Created@row) < 10, "000", IF(COUNTIFS(Created:Created, @cell = Created@row) < 100, "00", IF(COUNTIFS(Created:Created, @cell = Created@row) < 1000, "0"))) + COUNTIFS(Created:Created, @cell = Created@row)

• ✭✭
Options

Hi Paul,

The last number is repetitive.

• ✭✭
Options

Hi Paul,

I used the previous functions with month->day and year->month. Looks like its working.

="ECR-2021" + "-" + MONTH([Created On]@row) + "-" + DAY([Created On]@row) + "-" + IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 10, "000", IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 100, "00", IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 1000, "0"))) + COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row)))

• ✭✭✭✭✭✭
Options

I had initially started down that route but wanted to try to simplify it since we are now looking at the full date instead of just month and year.

But the more I look at the "simplified version" the more I realize it was flawed in a few different ways.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!