Compare CHILD cell value in a reference formula

Gina Smith

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?

Paul Newcome

Instead of

CHILD([Pending Decisions]@row

use

Category@row

Paul Newcome

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

Gina Smith

@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.

Paul Newcome

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

Gina Smith

@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?

Paul Newcome

Instead of

CHILD([Pending Decisions]@row

use

Category@row

Gina Smith

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.

Paul Newcome

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

Gina Smith

@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!

Paul Newcome

Happy to help. 👍️