Count 1, 2, 3 etc from a specific point in a column and then restart for replicated parent row

Hello community,

I would like a formula that creates a string of numbers starting at 1 every time I reach a new set of children in a row, but also skipping any group that has the name accessories or rack. I only want to count (starting from 1 each time) for the group that is "Equipment Master List" with the parent being 'Front'


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need to insert an auto number column (called "Auto"), a text/number column (called "Row Number"), and a text/number column (called "Parent").

    In the [Row Number] column you would use this column formula:

    =MATCH(Auto@row, Auto:Auto, 0)

    In the [Parent] column you would use this column formula:

    =IF(COUNT(CHILDREN([Group(1)]@row)) = 0, PARENT(Row@row))

    Then to get your count you would use something along the lines of

    =IF(CONTAINS("MASTER", [Group(1)]@row), COUNTIFS([Group(1)]:[Group(1)], CONTAINS("MASTER", @cell), Parent:Parent, @cell = Parent@row, [Row Number]:[Row Number], @cell<= [Row Number]@row))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. Two more columns. We will call them "Level 1" and "Level 2" simply because I haven't had enough coffee yet to be creative. Haha.

    In the [Level 1] column use this:

    =IF(COUNT(CHILDREN(Auto@row)) = 0, INDEX(ANCESTORS(Row@row), 1))

    This goes in the [Level 2] column:

    =IF(COUNT(CHILDREN(Auto@row)) = 0, INDEX(ANCESTORS(Row@row), 2))

    Then we adjust our COUNTIFS like so:

    =IF(Parent@row = "Back", "-", IF(CONTAINS("MASTER", [Group(1)]@row), COUNTIFS([Group(1)]:[Group(1)], CONTAINS("MASTER", @cell), Parent:Parent, @cell = Parent@row, [Level 1]:[Level 1], @cell = [Level 1]@row, [Level 2]:[Level 2], @cell = [Level 2]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row)))

    This should restart the count for each rack as well as each main parent Row (dark blue).


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!