Formula Help for Counting Children
I need help with a formula that will count children of a row and label them consecutively. Like this:
So if the section endnotes has three sections/children I want a formula to add numbers in consecutive order to the end based on counting the children rows.
al.g1.m1.tg.endnotes
al.g1.m1.tg.endnotes.1
al.g1.m1.tg.endnotes.2
al.g1.m1.tg.endnotes.3
Best Answer

We created the parent primary column to hold a unique identifier for each group. If the value in that is not unique to each group the numbers will not start over.
Replace the formula in parent primary with something that is unique to each section. We were using:
=PARENT([Primary Column]@row)
You might want to use another column instead, so the formula would be:
=PARENT([Unique Column]@row)
Or join the values from two columns to create something unique, using a formula like:
=PARENT([Primary Column]@row)+"  "+PARENT([Other Column]@row)
Answers

Hi @kpierson109
You need to add a couple of extra columns to your sheet, to do this. If you make the formula a column formula, you can hide both of these and forget about them.
1. An auto number column to number the rows. I call this Row Number. This enables you to write a formula based on where the child row is, in relation to other children, to get the sequence.
2. A column that contains an identifier from the parent row for each child row. I called this Parent Primary and used the formula below. This enables you to refer to each child's parent within the row (which is necessary to get our next formula to work).
=PARENT([Primary Column]@row)
I can't see your column headings so you might need to adjust this.
You should have something that looks like this:
3. Now you can use a formula like this to take the code that is in the parent row and iterate the number at the end for each child row.
=PARENT() + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row)
I have used this in the column I am calling "Code".
I've extended this to show how it works with multiple parents:
I hope this answers your questions.

Thank you!
=PARENT([Public Facing Title]@row) + COUNTIF([Parent Primary]@row:[Parent Primary]@row, @cell = [Parent Primary]@row, [Row #]@row:[Row #]@row, @cell <= [Row #]@row)
This is what I used based on my column names but it is saying invalid operation.

Hi
Almost there! There are three errors in the formula.
1
When you reference an entire column, the syntax is: column name colon column name, like this:
[Parent Primary]:[Parent Primary]
not
[Parent Primary]@row:[Parent Primary]@row
Where I had [Parent Primary]:[Parent Primary] you should also have [Parent Primary]:[Parent Primary] as you have not changed that column name.
2
The same applies to the row number column, where I had a column called Row Number and used
[Row Number]:[Row Number]
you have a column called Row # so must use
[Row #]:[Row #]
not
[Row #]@row:[Row #]@row
3
As you have more than one criteria you need to use a COUNTIFS function not a COUNTIF
Full formula
Pasting your column headings into the formula should look like this. The only change needed was to replace "Number" with "#", as shown in bold.
=PARENT() + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row Number]@row)
Specifying [Public Facing Title]@row is only needed if this formula is not in the [Public Facing Title] column. If it is not, then you do need to add that. If you need it, what you did was correct.
=PARENT([Public Facing Title]@row) + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row Number]@row)

THANK YOU! This worked. I am wondering if it is possible to represent something else. Basically, if there is only one child for a section, I want that name of that section to repeat.
al.g1.m1.tg.endnotes
al.g1.m1.tg.endnotes.1
Instead of the above, I would want it to be the following:
al.g1.m1.tg.endnotes
al.g1.m1.tg.endnotes.endnotes

You will need two more formula
A formula to find the text at the end of the parent cell, after (and including) the final period. We do this using a RIGHT and FIND function.
RIGHT(PARENT([Public Facing Title]@row), FIND(".", PARENT([Public Facing Title]@row)) + 2)
This is what you would like to be appended to the PARENT Public Facing Title if there is only one childn
A formula to count if the child is an only child. We do this using a COUNTIF and counting the Parent Primary column we created yesterday. We wrap this in an IF to execute the formula above, when this value is 1. This assumes the values in Parent Primary are never duplicated.
IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1
We can then put this IF into the formula we already have. So IF the child is not a sibling, use the RIGHT function to append the last part of the parent ID. If the child has a sibling, use the original formula.
Combined you have
=PARENT(Public Facing Title]@row) + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, RIGHT(PARENT([Public Facing Title]@row), FIND(".", PARENT([Public Facing Title]@row)) + 2), COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row))

Thank you! The formula is close to working. If there is only one child, I want it to exclude the number and just have the parent header.
Right now it is producing this for single children:
al.g1.m1.tg.summary
al.g1.m1.tg.summary.summary1
I want this:
al.g1.m1.tg.summary
al.g1.m1.tg.summary.summary
For more than one child it is showing this:
al.g1.m1.tg.texts
al.g1.m1.tg.texts.texts1
al.g1.m1.tg.texts.texts2
While if there is more than one child I want this:
al.g1.m1.tg.texts
al.g1.m1.tg.texts.1
al.g1.m1.tg.texts.2

Can you copy and paste the formula you entered here?
The formula supplied should do what you want with the exception of the periods before the numbers for the multiple child rows.
To add that period you can add it to the formula here (the part in bold):
=PARENT() + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, RIGHT(PARENT([Public Facing Title]@row), FIND(".", PARENT([Public Facing Title]@row)) + 2), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row))

Okay here is what was showing with this: =PARENT() + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, RIGHT(PARENT([Public Facing Title]@row), FIND(".", PARENT([Public Facing Title]@row)) + 2), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row))
al.g1.m1.tg.texts
al.g1.m1.tg.texts.al.g1.m1.tg.texts.1
al.g1.m1.tg.texts.al.g1.m1.tg.texts.2
I added this: PARENT([Public Facing Title]@row) + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, RIGHT(PARENT([Public Facing Title]@row), FIND(".", PARENT([Public Facing Title]@row)) + 2), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row))))))))))))
This led to this being produced:
al.g1.m1.tg.texts
al.g1.m1.tg.texts.texts1
al.g1.m1.tg.texts.texts2

Can you share what your parent headings are now?
The formula is taking the value in the parent (shown in red in the diagram)
then, if there is only 1 child, it adds the last part of the parent again (shown in blue)
if there are multiple children it adds a dot and a number (shown in green)
Here is the formula doing that
It isn't clear how this differs from what you want. Can you paste a screen shot? Or indicate what is in the parent row?

Got it to work! How would I incorporate removing spaces if the public facing title row is more than one word.
It is producing this:
al.g1.m1.tg.learninggoals
al.g1.m1.tg.learning goals.1
al.g1.m1.tg.learning goals.2
al.g1.m1.tg.learning goals.3
I want:
al.g1.m1.tg.learninggoals
al.g1.m1.tg.learninggoals.1
al.g1.m1.tg.learninggoals.2
al.g1.m1.tg.learninggoals.3

I still don't understand the examples you are sharing. Is al.g1.m1.tg.learninggoals the parent and al.g1.m1.tg.learning goals.1 the first child? Spaces should not be being introduced.
However, I can provide a formula to remove spaces if they exist in the parent row. So if your data looks like my mock up, the formula would be for all the child rows would be:
=SUBSTITUTE(PARENT([Public Facing Title]@row), " ", "") + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, "." + SUBSTITUTE(RIGHT(PARENT([Public Facing Title]@row), LEN(PARENT([Public Facing Title]@row))  FIND("*", SUBSTITUTE(PARENT([Public Facing Title]@row), ".", "*", LEN(PARENT([Public Facing Title]@row))  LEN(SUBSTITUTE(PARENT([Public Facing Title]@row), ".", ""))))), " ", ""), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row))


You can only have one auto number column per sheet. But you may be able to share the auto number column that you are using elsewhere. So long as it iteratively increases as the row numbers increase it will do what you need it to do.

Thanks! The numbering is working correctly in some sections of SmartSheet. As soon as I get to sections that are setup like this. The formula isn't working. I the learn subsection should be ".1", ".2", and ".3".

Can you share a screen shot that includes your auto number column?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!