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



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!