#NOMATCH returned on Sum(children())

Options

Hi all,

I'm a bit puzzled.

I'm using this formula where H1 is a column that identifies titles (0 being a child, 1 up to 5 being the hierarchical level) to sum the cases that are reported from the second part of the formula (If (Site Management OR Alkalin), AND (not completed and due date within 30 days....) put 1

And the formula returns a #NOMATCH :( when I transform the formula into Column formula.

Making a cell formula again, does not correct the problem.

Very strange indeed.

Anyone to help me on this?

=IF([H1]@row <> 0, SUM(CHILDREN()), IF(AND(OR([Hub]@row = "Site Management", [Hub]@row = "Alkalin"), Status@row <> "Completed", [Due Date]@row < TODAY() + [Short Term Actions in Days]#), 1))

Thanks in advance for your help.

Raoul

Answers

  • Louis Raoul
    Louis Raoul ✭✭✭✭
    Options

    Just for the clarity,

    • the #NOMATCH appears only at Title level instead of the Sum(Children()) intended.
    • The column containing Site Management or Alkalin [Hub] is filled in with an Index/match - although I cannot see a relation between the error and the [Hub] column.

    Thanks again for you help

    Raoul

  • Louis Raoul
    Louis Raoul ✭✭✭✭
    Options

    Hello again,

    After a good night I looked at my problem again and found that it is far more simple than expected. There were a couple of #NOMATCH that I missed in a very long list. Hence the Sum(children()) providing a #NOMATCH as a result.

    Not a big deal really.

    What I learned here that is that Sum(children()) does not ignore #NOMATCH as it would ignore a simple text cell.

    All the best

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Louis Raoul

    I hope you're well and safe!

    Glad you got it working! Easy to miss!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!