Count child rows that are "Complete" in a ONLY when its parent row is "In Progress"

System

This discussion was created from comments split from: COUNTIF with AND.

Kam114

Would someone be able to help me out with a similar issue?

We need to count every child row that has a value of "Complete" in a 'Status' Column ONLY when its parent row has a value of "In Progress" in the same 'Status' Column.

The formula that I used :

=IF(COUNTIFS([Hierarchy]:[Hierarchy], 0, [Status]:[Status], "In progress") > 0, COUNTIFS([Hierarchy]:[Hierarchy], 1, [Status]:[Status], "Complete"), 0)

results in an incorrect count as it looks for ANY parent row that has 'In Progress" when we need the formula to refer to just the dedicated parent of each child row.

Gia Thinh

Hi Kam114,

You may want to add a helper column, called Parent's Status, as shown in the screenshot below, with this column formula:

=PARENT(Status@row)

then count the number of complete task with the following formula. The expected result should be 7.

=COUNTIFS(Status:Status, "Complete", [Parent's Status]:[Parent's Status], "In Progress")

Hope it works for you.

Kam114

@Gia, thank you so much! That worked!