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:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    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)))



Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!