Count Raised flags under child and grandchildren

rstefanis41586 · 03/16/18

Hello everyone,

I am trying to wrap my head around this one and I'm sure others have too.

I want to count all the Flags that are raised under a grandfather (Administration) : the children and their grandchildren.

Currently my solution works, but it's weak. It depends on a fixed range, and as time goes on, new grandchildren and children are added. If the formula's range is not updated, important flags can go unreported.

Here's the formula:

=COUNTIF(Flag3:Flag27, 1) + " Flag(s) raised"

If I could somehow make this work by including the COUNT(CHILDREN()) formula it'd be nice but I can't wrap my head around it. Any feedback would be appreciated.

Thanks!

Ofir Marco · 03/18/18

I am also struggling counting with "Children" column based on criteria.

So i have done something similar using another calculation column ([Flag status]).

here the formula for the extra column: =IF(Flag2 = 1, 1, 0)

Here is the formula i used for the [Count flags] column:

=SUM(CHILDREN([Flag status]1))

It sums the additional column by children so that only when there is a parent it will sum the values.

Flag count.png

rstefanis41586 · 03/19/18

Hello Ofir,

Thanks for your insight. I was considering something like you, where I would "roll up" the sum of tasks (grandchildren) into children then sum children into the original parent.

I'm going to give your solution a try, but I hope in the future Smartsheets developers will figure out a way to do this in a simple formula.

Thanks!

Ofir Marco · 03/19/18

I hope so as well.

There are a few things that i wish could have been improved, this is one of them.

Meanwhile we can only think of some workarounds.

at least it keeps our minds sharp

L_123 · 03/19/18

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

I might have a solution. Let me know if this is what you were thinking of

Ofir Marco · 03/20/18

Can you explain the logic of your solution?

L_123 · 03/20/18

The flag is raised at the parent column if any of the children or grandchildren have a flag so you cannot miss any flags through the use of an if statement and a countif. The numbers on the right shouldn't be much different than yours, i just added an if statement to make them blank when not reporting a parent cell.

BeckyK · 06/01/19

Can you share your sheet again? I'm also looking for a way to count flags at the parent level.

Thanks!

Count Raised flags under child and grandchildren

Comments

Help Article Resources

Categories