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

Sam Swain

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'

Paul Newcome

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

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).

Paul Newcome

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))

Sam Swain

Thanks for your help again Paul!

It's not quite worked...although I have no information in the Auto Column yet.

First picture below shows the error - originally said circular reference when I did a single row. When I converted to column formula it changed to "#BLOCKED"

Second picture below shows where I've manually entered data to show you what the result should be

Sam Swain

Here's a more accurate reflection of your instructions - I didn't chose the auto column as an auto# column before

Paul Newcome

SO what formula do you have there in those cells that have the circular reference error?

Sam Swain

For ease, I have removed the formula from those cells and they are just text. This has stopped the circular reference and blocked messages. The formula in the # column ::

=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))

...now reads, INVALID and the AUTO seems to be counting from 1998. Do I need to change the formatting for the auto column maybe?

Paul Newcome

The auto number column is fine how it is. It is used to generate a unique entry on every row which allows us to generate the row number.

Do you have that error anywhere else in any of the other columns being referenced by the formula?

Sam Swain

Hi Paul, the answer was yes. Invalid for one of the rows in the Group(1) column.

The formula is now working perfectly. You are an absolute legend. Thank you so much! I know you're always "happy to help" but this community and I owe you so much. Thanks again man.

Sam Swain

Hold on Paul sorry haha. It's nearly working....

I need the count to restart every time we reach the children rows under when the parent row is 'Front'.

Anytime the parent row is called 'Back' a - should appear. I've added this into the formula already. Currently we have:

=IF(Parent@row = "Back", "-", 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)))

When I reach the next Rack, I need the count to restart from 1. Currently it is counting from 1 and continuing until it reaches the last row...

The 5 should be 1 again.

Paul Newcome

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).

Sam Swain

Works perfectly, thank you so much Paul

Paul Newcome

Happy to help. 👍️