Count Children Rows w/ Specific Statuses

Melissa Larkin

Hello,

I have a Status column that has the following drop-down items - To Do, In Progress, Done, and Future Work Cycle Task. I want to sum the # of child rows with the status of To Do or In Progress.

I tried =COUNTIFS(CHILDREN(), "To Do", CHILDREN(), "In Progress") and it doesn't work; however, when tried just =COUNTIFS(CHILDREN(), "To Do") it worked and tried =COUNTIFS(CHILDREN(), "In Progress") it worked. I just need these numbers to add together :(

Paul Newcome

Try this instead:

=COUNTIFS(CHILDREN(), OR(@cell = "To Do", @cell = "In Progress"))

Paul Newcome

Try this instead:

=COUNTIFS(CHILDREN(), OR(@cell = "To Do", @cell = "In Progress"))

Melissa Larkin

THANK YOU SO MUCH! It works. I really appreciate it.

Paul Newcome

Happy to help. 👍️

Ashley Greenwood

This formula has helped me with what I am working on so thanks for asking & thanks for responding!

I am trying to use a similar formula but I am looking for it to count in the parent row where multiple drop-down options can be selected in the same cell of the child row. Here is the formula I am using now:

=COUNTIFS(CHILDREN(), OR(@cell = "Line of Business 1", @cell = "Line of Business 2", @cell = "Line of Business 3", @cell = "Line of Business 4"))

This works great if only one of the drop-down options (found in the quotation marks) is selected however, if more than one responses are used it doesn't count that cell.

How can I amend the formula so that the cell is still counted if it contains either one of the drop-down options or if it contains more than one? (I hope I am making sense - if not, I apologize!)

Paul Newcome

@Ashley Greenwood Would you want it to count (for example) 2 if there are two of the possible selections made in the same cell, or would that just be a count of 1?

Ashley Greenwood

@PaulNewcome I would want it to only count once. Thank you.

Paul Newcome

@Ashley Greenwood In that case we just need to incorporate the HAS function.

=COUNTIFS(CHILDREN(), OR(HAS(@cell, "Line of Business 1"), HAS(@cell, "Line of Business 2"), HAS(@cell, "Line of Business 3"), HAS(@cell, "Line of Business 4")))

Ashley Greenwood

Thank you, Paul. It doesn't seem to be working, unfortunately.

Paul Newcome

@Ashley Greenwood Are you able to provide some screenshots?