#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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!