Count only if Parent
I have a [RowID] column that is populated by this formula =IF(ANCESTORS@row = 0, COUNT($ANCESTORS$1:$ANCESTORS@row, ""))
It returns sequential numbers but, I need it to only count if Ancestor = 0
Secondly, I need the result fill format to look like this 0000 (like an auto number)
0001
0002...
0100
0101
0102..
1000
Answers

To count only rows where ANCESTORS = 0, use COUNTIF or COUNTIFS to check the number of ancestors first.
A simplified formula would look something like this:
=IF(Ancestors@row = 0, COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), "")
For the fill, you can leverage the LEN function. If the row count is one digit, we need three 0s, if it's two digits, we need 2 0s etc. By using a Nested IF statement you can insert the correct number of leading 0s.
=IF(LEN(RowID@row) = 1, "000" + RowID@row, IF(LEN(RowID@row) = 2, "00" + RowID@row, IF(LEN(RowID@row) = 3, "0" + RowID@row, RowID@row)))
You can expand this for how ever many or few fill digits you need.
Let me know if you have any questions!
Schiff
Solution Implementation Manager
Smartsheet

Thanks Schiff. this works as expected.
Is there any way this could nested into one formula?
=IF(ANCESTORS@row = 0, "DMR" + COUNTIF(ANCESTORS$1:ANCESTORS@row, ANCESTORS@row = 0, "", (IF(LEN(RowID@row) = 1, "000" + RowID@row, IF(LEN(RowID@row) = 2, "00" + RowID@row, IF(LEN(RowID@row) = 3, "0" + RowID@row, RowID@row))))))
Help Article Resources
Categories
Check out the Formula Handbook template!