Using SUM with child rows

I have a parent row that I'm using to sum all child row values where Children = 0 and Status = "Not Started". This is my formula.

=IF(OR(ISBLANK(Status@row ), Hierarchy@row = 0), "", IF(Hierarchy@row = 1, SUM(CHILDREN()), IF(AND(Children@row = 0, Status@row = "Not Started"), 1, 0)))

However, if any of my task rows are indented, where that row is no longer a child, but a parent of other child tasks, the SUM breaks and doesn't calculate the correct number.

Once I remove the indent and all the rows are Children = 0 again, the SUM works, like below.

I need to know how I can sum all Children = 0 rows where they have a 1. I don't want to count any indented parent rows. Only the lowest level child tasks. Any suggestions?

Find more posts tagged with

Formulas

Project Management

Accepted answers

Paul Newcome

In that case you could use just a single column to indicate child rows and then use a row report grouped by status. Include the Summarize feature to get a count of each grouping in the report then use that report to create your chart widget.

=IF(COUNT(CHILDREN([Primary Column Name]@row)) = 0, 1)

If you drop that formula into a checkbox column, it will check the box for all child rows which you can then use in your report filter.

All comments

Matthew J McAteer

Hi @DTPM,

You can add a helper column that counts the number of Children for each row. Then use that to count only rows that have no Children.

I hope that helps!

Happy New Year!

Matthew

Paul Newcome

Try this formula instead:
=IF(COUNT(CHILDREN()) = 0, IF(Status@row = "Not Started", 1, 0), SUM(CHILDREN()))

What is your end goal for all of this? Are you using separate columns for each status and trying to get counts on a dashboard or something?

DTPM

Correct, Paul. Our senior leaders manage multiple projects in a single sheet. I'm trying to count up the total tasks for each type of status so I can display them on a dashboard. I'm tracking a Hierarchy, Parent, and Children column to help with knowing which rows are project related. I need to get the right child task count rolled up to the project parent row, whether or not the user is indenting certain rows. And I need it to be a column formula, which is why I need a formula that accounts for various hierarchies.

DTPM

Thanks, Matthew. I'm trying to count the rows that match a specific status, not just the total number of child rows.

Paul Newcome

=IF(COUNT(CHILDREN([Primary Column Name]@row)) = 0, 1)

If you drop that formula into a checkbox column, it will check the box for all child rows which you can then use in your report filter.

DTPM

Thanks, Paul. That's a good idea, too. I'll have to play around with that idea to see if there are any widget limitations on a dashboard by going through a report using Grouped By and Summarize with various charts. But I can see how that might work. Appreciate the tip!

I ended up figuring out how to get around one or more indents. It's by using the DESCENDANTS function to count the number of child rows that contains a 1, which is determined by comparing if the CHILDREN value at each row is 0 and the Status at each row is Not Started. That gives it the 1, which gets counted up to the project parent level.

=IF(OR(ISBLANK(Status@row ), Hierarchy@row = 0), "", IF(Hierarchy@row = 1, COUNTIF(DESCENDANTS(), 1), IF(AND(Children@row = 0, Status@row = "Not Started"), 1, 0)))

Again, since multiple projects are being managed in a single sheet, each Hierarchy of 1 is a different project that has different task status metrics that need to be counted and reported on a dashboard.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.