Descendants Formula

Mike Dawson

I am currently using a formula within our project management sheets to collect distinct dates for site visits, based on the type of site visit entered into another cell. I have a Parent Row, under which are nestled multiple children and descendants. The formula I'm using works just fine, but it's very time consuming to update into every row by specifying the range, and I have several summary cells that use similar formulas. Here's an example, which counts the number of scheduled visits we have completed:

=COUNT(DISTINCT(COLLECT([Complete / Confirmed Date]1685:[Complete / Confirmed Date]1863, [Visit Type]1685:[Visit Type]1863, "Scheduled")))

For reference, here's an example of the data I'm pulling from and below that, the Scheduled Visits Completed is the cell containing the formula above.

What I'd like to have is a formula I can just paste into all my parent rows that captures all the descendants without me having to manually adjust the range for Complete / Confirmed Date and Visit Type on every row (and again for all the other summary cells above). I've tried adding Descendants into this formula, but I'm obviously doing it wrong, as I can't seem to get it to work any way I try it; I'm hoping it's something simple I'm missing, if anyone has a suggestion!

jmyzk_cloudsmart_jp

Hi @Mike Dawson

You can use a formula something like this to count grandchildren with a certain status ("Not Started" in this example:

Not Started: =IF(Ancestors@row = 0, SUM(CHILDREN()), COUNTIF(CHILDREN(Status@row), "Not Started"))

https://app.smartsheet.com/b/publish?EQBCT=5a8af367c3e4499fa74f80cdcdbc89b0