Formula Help for Counting Children
Answers
-
Is the auto number column the one on the right of the screen shot? The column starting 197?
It looks like you have an extra level of hierarchy, not just one parent and child. Is that correct?
I have run out of hours today but will have a look tomorrow.
-
Yes, there are multiple parent and child. Would I restart the numbering?
I need the numbering to be based off subsection. So Learn is a child (section of Lesson 3) but it is a parent of Read, Respond, and Observe (subsections). I want to count the number of subsections within a section.
-
Is it because the subsection names are repeating? It seems like that is the case. If so is there a way for it to see each Learn as the first occurrence?
-
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)
-
Thank you! That worked!!
=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)))))))))))
Last thing, If I wanted to remove an ending if a parent has resources in it how would I do that?
Example:
al.g1.m1.ta.tg.about-the-images
al.g1.m1.ta.tg.end-notes
al.g1.m1.ta.tg.end-notes.1
al.g1.m1.ta.tg.end-notes.2
al.g1.m1.ta.tg.end-notes.3
al.g1.m1.ta.tg.resources
Instead of al.g1.m1.ta.tg.resources populating, I want ".resouces" removed.
al.g1.m1.ta.tg.
-
I am still not following your examples. Can you identify what the parent row is?
You can either add another IF to to formula to do something different if the parent row contains resources (which I think is where it is being pulled from). Or you can add another substitute to the formula to remove ".resources" wherever this appears within the output. This will remove all instances of ".resources".
=SUBSTITUTE(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)), ".resources", "")
-
Apologies. If public facing title is equal to "Resources" then I want the output to remove ".resources" which is currently being produced by the formula.
I tried this and moving the substitute around but it is still populating ".resources".
-
With the current formula, if the Public Facing Title is equal to Resources then a Single child would be Resources.Resources and multiple children would be Resources.1, Resources.2 etc. You could exclude any rows where the public facing title is equal to "Resources" by using another IF.
=IF(PARENT([Public Facing Title]@row) = "Resources", "", 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)))
This would return blank instead.
However, if you don't mean Public Facing Title is equal to Resources, but that Public Facing Title contains "Resources", then you need a slightly different formula.
=IF(CONTAINS("Resources", PARENT([Public Facing Title]@row)), "", 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)))
Again this will return blank instead.
If you want to return other parts of the Public Facing title you can replace "" with the function for that part.
Does that hekp?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!