How to Use the Smartsheet CHILDREN Project Management Formula

In Smartsheet project management, staying organized and maintaining clear hierarchies is essential for success. Thankfully Smartsheet offers a powerful CHILDREN formula that allows users to reference all the child rows in a cell's hierarchy. In this blog post, we will explore the CHILDREN formula and its various applications using other functions like COUNT, COUNTIF, SUM, SUMIF, and MAX.

Understanding the CHILDREN Formula

The CHILDREN formula in Smartsheet is designed to help users reference all the child rows under a specific parent cell in a hierarchy. While the ANCESTORS function refers to all the ancestors above a cell, CHILDREN focuses on the rows indented under the direct children, also known as grandchildren, are not automatically referenced by the CHILDREN function. This feature is particularly useful for project management, where tasks, subtasks, and categories are organized hierarchically.

Leveraging Hierarchy in Project Management

Hierarchical structures are widely used in project management to organize tasks, phases, and categories. For instance, project plans typically consist of phases and tasks associated with those phases. In this context, tasks serve as child rows to the respective phases, making it easier to manage and track project progress.

Even RAID (Risk, Action, Issue, Decision) logs can benefit from hierarchical organization. Here, the headers Risk, Action, Issue, and Decision act as parent rows, and the corresponding items are tracked per category and indented under them as child rows. This approach enhances the clarity and effectiveness of managing project-related risks, actions, issues, and decisions.

CHILDREN Formula in Combination with Other Functions

The real power of the CHILDREN formula lies in its ability to work seamlessly with other functions, enabling users to perform various calculations and aggregations based on the child-parent relationships.

COUNT Function

When used with the CHILDREN formula, COUNT enables users to determine the number of child rows under a specific parent. For example, if a phase named "Plan" has 5 tasks directly indented under it, the COUNT function would return 5. Any subtasks under these tasks would also be counted as children for the corresponding task. Additionally, COUNTIF can be used to narrow down the count to only children that meet certain criteria. For instance, you could count only the tasks that are not closed for a particular phase.

SUM Function

With the SUM function and the CHILDREN formula, users can aggregate numerical values associated with child rows to their respective parent rows. This aggregation is useful when tasks have cost or other numerical data associated with them. By using the SUM function, you can roll up all the task costs to the parent row, providing a clear overview of the total project cost. Similarly, SUMIF can be employed to sum only the children rows that meet specific criteria. For example, you could sum up only the closed tasks to calculate the total cost accrued to date.

MAX Function

When combined with the CHILDREN formula, the MAX function returns the highest value within a range of cells corresponding to child rows. This can be particularly helpful when you want to identify the most expensive tasks in a project. Alternatively, when working with a date range, MAX can be used to find the date of the last task scheduled to be completed, giving you a clear picture of the project timeline.

Practical Examples with Project Plan Sheet

To gain a better understanding of how the CHILDREN formula and its integration with other functions work in practice, you can watch our tutorial video. The video demonstrates specific examples using a project plan sheet, showcasing the applications of the CHILDREN formula with SUM and COUNT functions.

Improving Your Workflow with the Smartsheet CHILDREN Formula

The Smartsheet CHILDREN formula is a valuable tool for managing hierarchical data in project management. By using the CHILDREN function along with COUNT, COUNTIF, SUM, SUMIF, and MAX functions, users can efficiently calculate, aggregate, and analyze data related to child-parent relationships. Whether you are handling simple task lists or complex project plans, understanding and utilizing the CHILDREN formula can significantly enhance your project management capabilities.

Smartsheet Project Management Solutions from CloudWave

If you’re considering Smartsheet for project management and data analysis, look for a Smartsheet Platinum Partner like CloudWave who understands the technology best practices and prioritizes your unique business needs.

Through Smartsheet integration, we deliver cross-platform functionality​ that helps you bring your system together for an end-to-end solution within Smartsheet​. Using automation, we reduce tedious manual processes to save time and improve productivity.

CloudWave also provides Smartsheet support throughout the entire project lifecycle - defining scope, architecture, design, development and deployment.​ As your data environment constantly changes, we offer continued support beyond the project in an ever-shifting digital landscape.

To learn more about some of our successful Smartsheet projects, check out our case studies.

If you are looking for an experienced Smartsheet partner, reach out to our experts at CloudWave today.