Count Raised flags under child and grandchildren
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!
Comments

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.

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!

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

https://app.smartsheet.com/b/publish?EQBCT=cb5ea5d88ab14885abd7af9fd807d511
I might have a solution. Let me know if this is what you were thinking of

Can you explain the logic of your solution?

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.

Can you share your sheet again? I'm also looking for a way to count flags at the parent level.
Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!