Formula to Count Children Tasks Only

Options
Gabela
Gabela ✭✭✭
edited 12/14/23 in Formulas and Functions

I am looking for a formula to count Children tasks that meet specific criteria. I want to see how many tasks (Children only) do not have a task owner and a "LM" flag is set.

The formula below provides a count for ALL tasks (Parent and Children):

=COUNTIFS([Assigned To]:[Assigned To], "", Reporting:Reporting, <> "LM")

I can add a column (Is Parent) to the sheet to indicate that the task is a Parent or a Child using this formula:


=IF(COUNT(CHILDREN()) = 0, "N", "Y")

And then add this criterion to the main formula. 

=COUNTIFS([Assigned To]:[Assigned To], "", Reporting:Reporting, <>"LM", [Is Parent]:[Is Parent], "N")

I want to avoid adding a field to my sheet.

I tried using the formula below:

=IF(COUNT(CHILDREN([Task Name]:[Task Name])) > 0, COUNTIFS([Assigned To]:[Assigned To], "", Reporting:Reporting, <> "LM"))

However, this formula returns the same count as the formula above.

Thanks in advance for your help!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!