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!

Find more posts tagged with

Formulas

Comments

Ofir Marco

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

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

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

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

Can you explain the logic of your solution?

L_123

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

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

Thanks!

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…