Count children rows by status

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?

Screen shot.png

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.