#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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Roseann Joy Braley

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!