#Invalid Data Type: If Countifs Children Rows

Roseann Joy Braley

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!

Genevieve P.

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

Genevieve P.

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

Roseann Joy Braley

Geneviève - that's amazing, it worked! Thank you so much for your help. Have a great day.

Rosie

Genevieve P.

No problem, I'm glad that sorted it out! 🙂