How can I write a formula to summarize in parent row summaries in child rows?

Susan Cook

Column is entitled Status and is a drop down list with the following selections:

Not Started

In Progress

Delayed

Cancelled

Complete

I have a formula in each child row to summarize grandparent rows:

="Not Started (" + COUNTIF(CHILDREN(), "Not Started") + "), In Progress (" + COUNTIF(CHILDREN(), "In Progress") + "), Delayed (" + COUNTIF(CHILDREN(), "Delayed") + ") Cancelled (" + COUNTIF(CHILDREN(), "Cancelled") + ") Completed (" + COUNTIF(CHILDREN(), "Completed") + ")"

This gives me the following summary in each child row:

Not Started (0), In Progress (2), Delayed (1) Cancelled (0) Completed (0)

For the upper most parent row how do I summarize these summaries in the child rows so I have the same output as above but it is counting the summries of each child row?

Appreciate anyone's input/help!!!

Find more posts tagged with

Sheets

Formulas

Reports

Comments

Tim Meeks

Susan,

have you checked out these posts?

https://community.smartsheet.com/discussion/counting-all-siblings-below-master-parent

Susan Cook

Thanks Tim, I will check it out!

J. Craig Williams

The new @cell reference (in 2016-08-06) release allows this formula:

=COUNTIF(Status13:Status25, @cell = "Not Started")

in the grandparent (or great grandparent) to determine the number of Not Started's in each row - regardless of heirarchy status.

It requires abandoning the usefulness of the CHILDREN() function, and thus having to know the size of the data, but I'm still testing to find the solution.