Count children rows by status

christy.thompson106676

Hi - I have a sheet with parent rows and children rows.  Each child row is associated with a status (Not Started, In Progress, and Complete).  For each parent row, I'd like to count the number of child rows that are in each status.  

I need to present this information in a dashboard .  Ideally I would create a separate summary sheet and add the formulas there but I believe I read that you can't use the hierarchy formulas in a cross sheet reference so then I thought about creating hidden columns in my sheet - one  column for each status - to calculate the counts and put the formula on each parent row to count the children.

I got as far as the formula below:

=COUNTIF(CHILDREN(Status:Status), "Not Started")

But it's calculating the number of ALL tasks in the sheet with status of Not Started.  I only want it to calculate the number of tasks under the Compensation Adjustment parent row with status of Not Started.  Any ideas?

CaseyDuBose

You are correct. You cannot do a cross-sheet reference with something like a children({crosssheetref}). You need to reference the range of the specific parent row. 

I'd recommend you create the metrics section within the source sheet itself. You can put it at the top and use a helper column labeled metrics to filter it out of reports. 

See the following example I built: https://app.smartsheet.com/b/publish?EQBCT=616d3471090441fa99bae0da33fbbcff

--

casey@dubose.haus

Paul Newcome

Try replacing the column reference with a cell reference that points to the parent cell. For example...

 

If the parent row is row 53, you would use your same formula but replace Status:Status with Status53.

 

=COUNTIF(CHILDREN(Status53), "Not Started")

christy.thompson106676

Thanks Paul - that did it!

Paul Newcome

Excellent! Happy to help! 

Daniel Montoya

@Paul Newcome I see that you were able to help with this issue. I have a similar problem that I need help on. I tried doing what you recommended above but I had no luck. Is there any way I could schedule some time with you to show you what I am referring to?

Paul Newcome

@Daniel Montoya Are you able to provide screenshots with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

Phil Wightman

HI @Casey DuBose (Jalubro Consulting)

I cant access the link above as it says I'm not in the organisation. Can you provide in a different format?

Thanks

Phil

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