# Any ideas why a formula wouldn't work on all rows?

Options
edited 05/13/24

I have a formula that is working on at least one row, but not all rows, and I have the necessary data in all rows for it to work, so I am boggled as to why it isn't working on all rows

:

=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Blue")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Blue", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Red", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Yellow", "Green"))))))

• ✭✭✭✭✭✭
Options

As it is a nested IF formula, it is probably working for rows that evaluate to true in one of the earlier IFs, then failing when it reaches the part of the formula that has a problem in it. I would change all the outcomes to unique words and test it. See which words appear and which do not. The problem will be after the part of the formula that returns the last word. You can then focus on troubleshooting that part.

Something like this:

=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "cow", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "pig", IF(COUNTIF(CHILDREN(), "Green") > 0, "Emu", "Goat")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Rabbit", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Chick", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Hedgehog", "Mink"))))))

• ✭✭✭✭✭✭
Options

As it is a nested IF formula, it is probably working for rows that evaluate to true in one of the earlier IFs, then failing when it reaches the part of the formula that has a problem in it. I would change all the outcomes to unique words and test it. See which words appear and which do not. The problem will be after the part of the formula that returns the last word. You can then focus on troubleshooting that part.

Something like this:

=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "cow", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "pig", IF(COUNTIF(CHILDREN(), "Green") > 0, "Emu", "Goat")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Rabbit", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Chick", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Hedgehog", "Mink"))))))

• Options

That's a great idea, thanks!

• Options

Your suggestion helped me find the problem and fix it, thanks again!

• ✭✭✭✭✭✭
Options

Wonderful! Glad I could point you in the right direction.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!