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

Heather Duff

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

meliberi222

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

meliberi222

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”))))

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

meliberi222

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

Heather Duff

@meliberi222 Happy to help! Have a great week.