COUNTIFS children rows only (reference another sheet)

Heather P.

I am trying to write a formula that references another sheet and counts the number of blank cells in the Status column only for child rows whenever the Test Case ID = "ORC-01". In this example, the result should be 1.

I've tried the following formulas, but they both return an #UNPARSEABLE error:

=COUNTIFS(CHILDREN({Status Range}, "", {Test Case ID Range}, CHILDREN([Test Case ID]@row)))

=COUNTIFS(CHILDREN({Status Range}, "", CHILDREN({Test Case ID Range}, "ORC-01")))

Thank you in advance for the guidance,

Heather

Kelly Moore

Hello @Heather P.

To decipher the CHILD rows on another sheet, you'll need a helper column on your source sheet to indicate the hierarchical level of the row(Parent row vs child row).

You can call the column whatever you like, I'll refer to it as Level.

=COUNT(ANCESTORS([Test Case ID]@row))

If your sheet is laid out as your screenshot suggests, the Child rows will have a Level value of 1.

=COUNTIFS({Test Case ID Range}, "ORC-01", {Level}, 1, {Status Range}, @cell="")

Will this work for you

Kelly

PS: Depending on how your target sheet is laid out you might be dynamically name the Test Case ID rather than hard coding it. For instance, if your target sheet has it's own Test Case ID column where you manually inserted the IDs you want to track then the formula would become:

=COUNTIFS({Test Case ID Range}, [Test Case ID]@row, {Level}, 1, {Status Range}, @cell="")