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...
Thanks
Mel
*Smartsheet novice but intermediate Excel user*
Best Answer

Hi @meliberi222
Eureka! I've found another missing @row. See bold below.
=IF(CountIfPass@row = [Count Children Per Parent]@row, “Pass”, IF(AND(CountIfBlocked@row >0, CountIfFail@row = 0), “Blocked”, IF(CountIfNotRun@row = [Count Children Per Parent]@row, “Not Run”, IF(CountIfFail@row > 0, “Failed”))))
Also, I had to manually type it in  it wouldn't work copying and pasting it. If the formula still shows unparseable, try manually typing it. Once it works in one cell, you can copy and paste, drag fill, or convert it to a column formula.
Let me know if it works!
Best,
Heather
Answers

Hi @meliberi222 ,
This may fix it  you're missing an @row in the formula. I put it in bold below. Let me know if that fixes it.
=IF(CountIfPass@row = [Count Children Per Parent]@row, “Pass”, IF(CountIfBlocked@row = [Count Children Per Parent]@row, “Blocked”, IF(CountIfNotRun@row = [Count Children Per Parent]@row, “Not Run”, IF(CountIfFailed@row > 0, “Fail”))))
Best,
Heather

Hi Heather,
Unfortunately, I still get #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, “Failed”))))
Just wondering whether the logic is not right now...
So I want to automatically set the test outcome to 
Pass  if all test steps (child rows) passed
Blocked  if any of the test steps are blocked (regardless of passes) as long as there are no fails (don't care about passes or steps not run with this outcome)
Not Run  if all test steps are not run
Fail  if one or more test steps fail
and I come up with this formula....
=IF(CountIfPass@row = [Count Children Per Parent]@row, “Pass”, IF(AND(CountIfBlocked@row >0, “Blocked”, CountIfFail@row = 0), IF(CountIfNotRun = [Count Children Per Parent]@row, “Not Run”, IF(CountIfFail@row > 0, “Failed”))))
However, this still does not work. Am I even writing the formula correctly?
Thanks again
Mel

Correction but still doesnt work...
=IF(CountIfPass@row = [Count Children Per Parent]@row, “Pass”, IF(AND(CountIfBlocked@row >0, CountIfFail@row = 0), “Blocked”, IF(CountIfNotRun = [Count Children Per Parent]@row, “Not Run”, IF(CountIfFail@row > 0, “Failed”))))

Hi @meliberi222
Eureka! I've found another missing @row. See bold below.
=IF(CountIfPass@row = [Count Children Per Parent]@row, “Pass”, IF(AND(CountIfBlocked@row >0, CountIfFail@row = 0), “Blocked”, IF(CountIfNotRun@row = [Count Children Per Parent]@row, “Not Run”, IF(CountIfFail@row > 0, “Failed”))))
Also, I had to manually type it in  it wouldn't work copying and pasting it. If the formula still shows unparseable, try manually typing it. Once it works in one cell, you can copy and paste, drag fill, or convert it to a column formula.
Let me know if it works!
Best,
Heather

Oh my goodness, it has now worked! Thank you so much @Heather Duff .

@meliberi222 Happy to help! Have a great week.
Help Article Resources
Categories
Check out the Formula Handbook template!