Descendants Formula
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!
Answers
-
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"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!