Formula Help for Counting Children

kpierson109

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

Screenshot 2024-03-05 at 3.17.58 PM.png

KPH

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)

KPH

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.

kpierson109

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.

KPH

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)

kpierson109

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

KPH

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

kpierson109

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

KPH

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

kpierson109

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

KPH

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?

kpierson109

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

KPH

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

kpierson109

KPH

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.

kpierson109

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

KPH

Can you share a screen shot that includes your auto number column?