COUNTIFS children rows only (reference another sheet)
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
Answers
-
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="")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!