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

TitleVI2023
TitleVI2023 ✭✭
edited 11/21/23 in Formulas and Functions

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"))))

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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"))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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"))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!