Mastering Parent-Child Row Relationships: Solving the '#INVALID DATA TYPE' Dilemma in Health Status

TitleVI2023

I am working on a sheet where I want the parent rows to reflect the health status of their child rows. I have defined four categories, which are "No Risks," "Some Risks," "Overdue / Risks," and "Canceled / Done / On Hold." I have created a formula to achieve this, but I want to establish a rule that only when ALL child rows indicate "No Risks" or "Cancel / Done / On Hold," the parent row should mean "No Risk" or "Cancel / Done / On Hold," respectively. Otherwise, the parent row should show "Some Risk," superseded by "Overdue Risk." Can someone help identify the problem and help me rework the formula, the error message is: "#INVALID DATA TYPE" when all child rows are "No Risk".

=IF(COUNTIF(CHILDREN(), "Overdue / Risks") >= 1, "Overdue / Risks", IF(COUNTIF(CHILDREN(), "Some Risks") >= 1, "Some Risks", IF(COUNTIF(CHILDREN(), "No Risks"), "No Risks", IF(COUNTIF(CHILDREN(), "Canceled / Done / On Hold") > 1, "Canceled / Done / On Hold"))))

Paul Newcome

Try this:

=IF(COUNTIFS(CHILDREN(), "Overdue / Risks") >= 1, "Overdue / Risks", IF(COUNTIFS(CHILDREN(), "Some Risks") >= 1, "Some Risks", IF(COUNTIFS(CHILDREN(), "No Risks") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "No Risks", IF(COUNTIF(CHILDREN(), "Canceled / Done / On Hold") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Canceled / Done / On Hold", "Some Risks"))))

Paul Newcome

Try this:

=IF(COUNTIFS(CHILDREN(), "Overdue / Risks") >= 1, "Overdue / Risks", IF(COUNTIFS(CHILDREN(), "Some Risks") >= 1, "Some Risks", IF(COUNTIFS(CHILDREN(), "No Risks") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "No Risks", IF(COUNTIF(CHILDREN(), "Canceled / Done / On Hold") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Canceled / Done / On Hold", "Some Risks"))))