Metric for number of incomplete parent tasks on dashboard

Nicole Petersen

Hi everyone,

I'm trying to figure out a way to add a metric to my dashboard that will count the number of incomplete parent tasks across all of my sheets.

I've gotten as far as creating a report that filters down to just the parent tasks that haven't been marked to "complete", but haven't been able to figure out a function to count the number of rows on this report.

I tried to create a metric sheet, using the COUNTIF() formula, but it would not let me reference the report. Anyone discovered any workarounds for this?

Thanks!

Paul Newcome

You would need to insert a helper column on the sheet that marks parent tasks.

Then you would use a COUNTIFS on your metrics sheet to count how many boxes are checked with a status that is not complete.

Paul Newcome

You would need to insert a helper column on the sheet that marks parent tasks.

Then you would use a COUNTIFS on your metrics sheet to count how many boxes are checked with a status that is not complete.

Nicole Petersen

Paul,

Thank you! Is there a formula that will auto check the box if the row is a parent? It seems like a hassle for my team to have to check the box every time they add a new parent task.

Paul Newcome

There are a few different formulas depending on your layout and how many different levels of hierarchy you have.

Will you need to also capture certain rows that are at the parent level but won't have any child rows?

Nicole Petersen

Paul,

We only have one level of hierarchy, just parents and one level of child rows. Yes, we also need to capture the rows that are at the parent level but don't have children.

Paul Newcome

Ok. So insert a checkbox column and enter:

=IF(COUNT(ANCESTORS()) = 0, 1)

This will check the box for all "non-child" rows.

Nicole Petersen

Thank you! Sorry for actually giving you misinformation. We have three levels of hierarchy, as parent tasks fall under a category divider as well. Would the formula -IF(COUNT(ANCESTORS())=1,2) work to check boxes of the second and third level of hierarchy?

Paul Newcome

Try this...

IF(OR(COUNT(ANCESTORS()) = 1, COUNT(ANCESTORS()) = 2), 1)

This will check the box for all rows that are levels 2 or 3.

Nicole Petersen

That is selecting all my third level hierarchy child tasks as well. Is there a way to get it to check just the second level parent rows?

Paul Newcome

My apologies. You last comment said something about checking both. Here it is for just level two...

=IF(COUNT(ANCESTORS()) = 1, 1)

Nicole Petersen

That worked great, thank you so much for your help!

Paul Newcome

Happy to help. 👍️

Nicole Petersen

Can you also help me with the COUNTIFS formula for the metric sheet?

I've tried: =COUNTIFS(Metric:Metric,"True",[Status]:[Status],"In Progress) with Metric as my checkbox column, but it's not working.

Ideally, I'd like to write it to be Status is not "completed" but am not sure how to translate that into a formula.

Paul Newcome

If you are using a different sheet for your metrics, you are going to need to follow the appropriate steps for creating cross sheet references, and you will end up using a formula such as...

=COUNTIFS({Data Sheet Metrics Column}, 1, {Data Sheet Status Column}, @cell <> "Completed")

Nicole Petersen

Yes, that's exactly what I needed! Thanks

Paul Newcome

Happy to help. 👍️