Nested IFs wont work
I am trying to auto populate a text/number field with the status of Pass, Blocked, Not Run or Failed depending on the status various child records. I have calculated the number of children per parent (Count Children Per Parent) and calculated the number of child passes, child blocked, child run and child failed etc.
Example: The values calculated for the Parent are -
Count Children Per Parent = 9
CountIfPass = 3
CountIfFail = 1
CountIfNotRun = 1
CountIfBlocked = 4
So I am trying to use the following nested IF statement so that if the Count of Passes = Count of Children Per Parent I want to set the text/number field to "Pass" and so on for each of the 3 outcomes but naturally, if there are any fails then the parent is a "Fail".
Here's the calculation that brings up #unparseable....
=IF(CountIfPass@row = [Count Children Per Parent]@row, “Pass”, IF(CountIfBlocked@row = [Count Children Per Parent]@row, “Blocked”, IF(CountIfNotRun = [Count Children Per Parent]@row, “Not Run”, IF(CountIfFailed@row > 0, “Fail”))))
I'd really appreciate your assistance in helping me fix the calculation so I get the expected result in this example ("Failed")...unless of course there is an easier way...
*Smartsheet novice but intermediate Excel user*
Help Article Resources
Check out the Formula Handbook template!