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