# 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

Tags:

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!