Compare CHILD cell value in a reference formula
Hello, I have two sheets: a tracker sheet and a metrics sheet. In the metrics sheet, I am trying to calculate a variety of metric values where I need to match a CHILD cell value to a value referenced in the tracker sheet.
Here is the current formula that works as is
=COUNTIFS({Activity}, "Decision", {University}, Category25, {Status}, "Pending", {TD  SD}, >=0, {TD  SD}, <30)
Here is the formula where I am trying to substitute CHILD for Category25 that returns an #UNPARSEABLE error
=COUNTIFS({Activity}, "Decision", {University}, CHILD([Pending Decisions]@row, {Status}, "Pending", {TD  SD}, >=0, {TD  SD}, <30)
Here is a screenshots of my Tracker sheet where the University column is referenced in the formula as {University}
Here is a screenshot of the Metrics sheet
I am trying, in the formula in the Last 30 Days column, to match Tracker sheet, University column value of "Averett" to the metric sheet PARENT Pending Decisions, CHILD value of Averett.
Thanks everyone for your help in advance?
Best Answer

Instead of
CHILD([Pending Decisions]@row
use
Category@row
Answers

Do you have a [Pending Decisions] column in your metrics sheet where the formula lives?

@Paul Newcome  yes, I have a Status column that is out of the screenshot that contains either Pending, Closed in Agreement, or Closed in Dispute. All parts of the first formula work. All part of the second formula work and the only changed values are the ones I bolded. I have tried ANCESTER and CHILD and I get the same error on both.

Right. But do you have a column actually called "Pending Decisions"?

@Paul Newcome  no, Pending Decisions is the name of the Parent Row (of the Category column) that the Averett child falls under. The formula lives in the Count column indicated by the red box  both of which are demonstrated in the 2nd screenshot of the Metrics Sheet. Does that make sense?

Instead of
CHILD([Pending Decisions]@row
use
Category@row

Thank you for looking at this with me Paul. The issue with Category@row is I have more than one PARENT section that contains the name of each university. For example, I have a PARENT section called University which contains each university and I have a PARENT section called Pending Decisions which contains each university so, in this example, Averett is a row of data in each PARENT section. I currently use Category@row in the first section, University, and it works great but when I get to the next section, Pending Decision, I need it to find the Averett row in the second section which is why I was trying to figure out how to use PARENT, CHILD, ANCESTOR, etc.

But your formula is looking at the Status in the source sheet to determine which status to pull the count for.

@Paul Newcome  oh, duh?? My apologies. I was thinking category@row was going to pull the first match it encountered but it always pulls the "category" from the "@row" where it exists. I was able to confirm this through testing my changing the "Averett" in each row to "Averett1" where I could see the remaining rows returning the proper calculated values. Thank you!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!