# Formula to Count a value and create Sequence number stops and repeats

Options
✭✭✭

In an source sheet we have list of countries. If India is twice and in destination sheet it is 6 times but the value I need it in sequence i.e., 1,2 and starts again from 1,2 and again 1,2. US is at 3 times in souce hence in destination sheet 1,2,3 and repeats.

Tags:

• ✭✭✭✭✭✭
Options

You are going to need an auto-number column with no special formatting. Then you can use:

=IF(MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)) = 0, COUNTIFS(Country:Country, @cell = [Country 2]@row), MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)))

• ✭✭✭
Options
• ✭✭✭✭✭✭
Options

You are going to need an auto-number column with no special formatting. Then you can use:

=IF(MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)) = 0, COUNTIFS(Country:Country, @cell = [Country 2]@row), MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)))

• ✭✭✭
Options

@Paul Newcome Perfect Thanks lot.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!