#NOMATCH returned on Sum(children())
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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 142 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!