Count only if Parent

Detrie Zacharias

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

Schiff A.

Hi @Stavros_McGillicuddy,

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

Detrie Zacharias

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