#Invalid Data Type: If Countifs Children Rows
Hello Smartsheet Community,
I've spent several hours trying to determine the source of the #Invalid Data Type error that appears only under certain conditions. Context: I'm using this formula as a helper for conditional formatting and it works perfectly in some instances but I get #Invalid Data in others. Many, many tests later it seems that if the Finish dates are too far in the past, the error occurs, but not if some children Finish dates are today or in the future. This might be a fluke, but I truly can't find any other common denominator.
=IF(COUNTIFS(CHILDREN(Finish@row), <TODAY(), CHILDREN(Status@row), <>"complete"), 1, 0)
See below: 2 screenshots of the same sheet, just changed the start date and boom, no more error...
Important to note that when the formula does work, Helper successfully generates a '1'.
Any idea how I can fix this?
Start date: October 2020
Start date: April 2021
Thanks in advance!
Best Answer
-
It looks like you're just missing part of the logic statement... you ask the formula to COUNT, but then there's no instruction in regards to what that number is. I presume you want it to return 1 if the COUNT returns a number greater than or equal to 1?
=IF(Formula >= 1, 1, 0)
Try this:
=IF(COUNTIFS(CHILDREN(Finish@row), <TODAY(), CHILDREN(Status@row), <>"complete") >= 1, 1, 0)
Let me know if this works!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
It looks like you're just missing part of the logic statement... you ask the formula to COUNT, but then there's no instruction in regards to what that number is. I presume you want it to return 1 if the COUNT returns a number greater than or equal to 1?
=IF(Formula >= 1, 1, 0)
Try this:
=IF(COUNTIFS(CHILDREN(Finish@row), <TODAY(), CHILDREN(Status@row), <>"complete") >= 1, 1, 0)
Let me know if this works!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Geneviève - that's amazing, it worked! Thank you so much for your help. Have a great day.
Rosie
-
No problem, I'm glad that sorted it out! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!