Nested IFs wont work

meliberi222

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*

Heather Duff

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