Formula to Count Children Tasks Only

Gabela

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!

Eric Law

https://community.smartsheet.com/discussion/114370/formula-to-count-children-tasks-only

Try the following, which will eliminate the Is Parent column, =COUNTIFS([Assigned To]:[Assigned To], "", Reporting:Reporting, <> "LM", Reporting:Reporting, COUNT(CHILDREN(@cell))=0)

If you want to keep the Is Parent column, =COUNTIFS([Assigned To]:[Assigned To], "", Reporting:Reporting, <> "LM", [Is Parent]:[Is Parent], "N")

KPH

If you do not want to add a helper column to the sheet to identify the parent rows, is there anything already on the sheet in an existing column, that identifies them? Maybe a naming convention that is used? If so you could add another criteria, for example if the primary column included the word "parent", this would work

=COUNTIFS([Assigned To]:[Assigned To], "", Reporting:Reporting, <>"LM", [Primary Column]:[Primary Column], CONTAINS("parent", @cell))

Gabela

https://community.smartsheet.com/discussion/comment/409684#Comment_409684

Eric,

Thanks for the feedback. When entering the formula I get the famous #UNPARSEABLE message. Also, didn't include one important requirement; I need to add the formula in a field in the Sheet Summary and not the main sheet. I get the #UNPARSEABLE message in both locations.

Gabela

https://community.smartsheet.com/discussion/comment/409685#Comment_409685

KPH,

Thanks for the feedback; unfortunately, I don't have anything else that will distinguish a task as a parent or a child.

Eric Law

https://community.smartsheet.com/discussion/comment/409702#Comment_409702

Gotcha! I tweaked a few things so here you are.

=COUNTIFS([Assigned To]:[Assigned To], ISBLANK(@cell), Reporting:Reporting, NOT(@cell = "LM"), [Is Parent]:[Is Parent], "N")

NOTE, for the reporting column, you can use <> for not, which will only evaluate non-blank cells that are not LM, or you can use NOT(@cell= "LM") which will also look at blank cells (this is my preferred so am using it, lol)

Gabela

@Eric Law ,

Thank you for the response and the new formula!