How to count child rows based on assigned to and status

Kristin Fortner

I need a formula that counts child rows (rows that are unchecked in the Parent column), assigned to and status. See screenshot for reference

Tomasz Giba

Hi Kristin,

I may misinterpreted what you're trying to achieve but if you are looking for 3 different formulas here is the solution:

1) counting unchecked check boxes (type into the parent row in the same column):

=COUNTIF(CHILDREN(), 0) + ""

2) count assigned:

=COUNT(CHILDREN()) + ""

3) count any status (the same formula as above):

=COUNT(CHILDREN()) + ""

If this is not what you're looking for, please provide more specific description and we will try to help.

Kristin Fortner

Hi Tomasz,

Thank you for your response. I need to count how many tasks are to certain people, then separately count the number of unassigned tasks, with a status of "In Progress", "Not Started", or "Completed", but I don't want it to count the parent rows.

I am using the following formula currently: =COUNTIFS({*IS INDUCTION - NEW DESIGN TASK MANAGEMENT Range 1}, <>"", {IS INDUCTION - PROJECT PLAN Range 1}, @cell = [Full Name]@row, {IS INDUCTION - PROJECT PLAN Range 2}, OR(@cell = "Not Started", @cell = "Page Created"))

{*IS INDUCTION - NEW DESIGN TASK MANAGEMENT Range 1}, <>"" is supposed to only count if there is a value in the task column.

{IS INDUCTION - PROJECT PLAN Range 1}, @cell = [Full Name]@row, counts the assigned to person

{IS INDUCTION - PROJECT PLAN Range 2}, OR(@cell = "Not Started", @cell = "Page Created")) counts status.

Right now it is including the parent rows in the count. I need it to not include the parent rows.

Tomasz Giba

Hi @Kristin Fortner

Please check if screenshot below is close what your goal is?

Here is how it's done:

in the PLAN sheet I created additional helper column formula that detects the hierarchy level:

=COUNT(ANCESTORS(Description@row))

Formulas in the COUNT SHEET:

I named all Reference Ranges to IS INDUCTION - PROJECT PLAN as a column I referenced:

All formulas in the [Task Count] Column:

TOTAL TASKS:

=COUNTIFS({IS INDUCTION - PROJECT PLAN Hierarchy Level}, 2)

TOTAL TASKS In Progress

=COUNTIFS({IS INDUCTION - PROJECT PLAN Status}, Status@row, {IS INDUCTION - PROJECT PLAN Hierarchy Level}, 2)

and drag copy down to Completed

UNASIGNED TOTAL

=COUNTIFS({IS INDUCTION - PROJECT PLAN Assigned}, "", {IS INDUCTION - PROJECT PLAN Hierarchy Level}, >1)

UNASIGNED In Progress

=COUNTIFS({IS INDUCTION - PROJECT PLAN Assigned}, "", {IS INDUCTION - PROJECT PLAN Status}, Status@row, {IS INDUCTION - PROJECT PLAN Hierarchy Level}, 2)

and drag copy down to Completed

Jan Kowalski TOTAL

=COUNTIFS({IS INDUCTION - PROJECT PLAN Assigned}, [Assigned To]@row, {IS INDUCTION - PROJECT PLAN Hierarchy Level}, 2)

Jan Kowalski In Progress

=COUNTIFS({IS INDUCTION - PROJECT PLAN Assigned}, [Assigned To]@row, {IS INDUCTION - PROJECT PLAN Status}, Status@row)

and drag copy down to Completed

Copy two above formulas to other workers.

Hope that will help.