I want to count number of days in a separate column,,,

TeeM

I want to count the number of days in a separate column. I have a "Primary Column" with a parent row that counts the number of Weeks. The 'CHILDREN' rows are for each Week Ending with working days as 'GRANDCHILD' rows.

I have a separate column named 'Days Count' where I want to count the total 'GRANDCHILD' rows in the 'Primary Column'.

I have tried various formulas but they have all been unsuccessful. Can someone help?

Screenshot 2024-11-16 185759.png

kowal

hi @TeeM;

What about creating helper column called e.g. "Amount of Children" and use the formula in it =count(children(name_of_primary_column));

it will show 0 for all the grandchildren and in the extra column you use: =countif("range", 0) so it will give you number of grandchildren.

Hope it helps.

TeeM

Hi @Tomasz Kowalski,

Unfortunately, that doesn't seem to work

Jennifer Kurtz

@TeeM - Another idea you could try would be to add a column called "Level" and count the number of Ancestors. That would identify which rows are your "child" rows, "grandchild" rows, etc. - and you could reference this in your formula to count the number of days/grandchild rows.

Your "Level" column would look like this: =COUNT(ANCESTORS([Primary Column]@row))

and your "Days Count" column could look like this: =IF(Level@row=1, COUNT(CHILDREN(level@row)))

The "Days Count" formula will only count the number of children if the Level is 1 — your "week ending" rows. If you wanted the total to roll all the way up, you could add another IF statement:

=IF(Level@row=1, COUNT(CHILDREN(Level@row)), IF(Level@row=0, SUM(CHILDREN([Days Count]@row))))

The second "If" statement will sum your "week ending" rows in the parent row.

I'm not sure if this is exactly what you're after, though, so let us know! :)