Trouble with Nested IF Formula

kkinman

Hi,

Can anyone help me understand why the formula below isn't working properly? It's only returning results of "No Start" or "In Progress" and not understanding where the error might be happening.

=IF(NOT(ISBLANK([Last Name]@row)), IF(COUNTIFS(BG@row:[Lic/Cert]@row, @cell = " ") = 8, "Not Started", IF(COUNTIFS(BG@row:[Lic/Cert]@row, OR(@ )) = 8, "Complete", IF([Did Not Start Reason]@row <> "", "No Start", "In Progress"))))

Paul Newcome

I see the issue with the original formula. That closing parenthesis after the second 7 should have been moved to after "Gray". You closed the OR, but that second parenthesis needed to close the COUNTIFS.

kkinman

Sorry, the correct formula is here:

=IF(NOT(ISBLANK([Last Name]@row)), IF(COUNTIFS(BG@row:[Lic/Cert]@row, @cell = "") = 8, "Not Started", IF(COUNTIFS(BG@row:[Lic/Cert]@row, OR(@cell = "Green", @cell = "Gray")) = 8, "Complete", IF([Did Not Start Reason]@row <> "", "No Start", "In Progress"))))

Paul Newcome

Can you provide a screenshot of a few rows where it is not behaving as expected as well as describe all of the expected behavior for the formula?

kkinman

Definitely! Here is a screenshot: essentially what should happen is that if every column between BG and CPR is green or gray, the status should be "Complete". It's a little convoluted, but other scenarios would be that if Did Not Start Reason column is not blank, status should be "No Start", otherwise status should be "In Progress". Let me know if I can provide any additional info.

Paul Newcome

You are telling it to flag if the COUNTIFS equals 8, but you are only counting 7 columns.

kkinman

Thank you! Completely overlooked that. It's still not registering that some should be showing as "Complete"; could it be another issue with COUNTIFS or the OR portion of the formula?

Paul Newcome

What exactly is your new formula, and do you have a screenshot?

kkinman

Yes, here is the updated formula and screenshot below: =IF(NOT(ISBLANK([Last Name]@row)), IF(COUNTIFS(BG@row:[Lic/Cert]@row, @cell = "") = 7, "Not Started", IF(COUNTIFS(BG@row:[Lic/Cert]@row, OR(@cell = "Green", @cell = "Gray" ) = 7), "Complete", IF([Did Not Start Reason]@row <> "", "No Start", "In Progress")))). Status for all should be reading as "Complete".

Paul Newcome

What happens if you remove that closing parenthesis after the second 7?

kkinman

Unfortunately, that was leading to a syntax error. I continued to tinker with it and was able to land on this solution which seems to have fixed the issue. Thank you for working through this!

=IF(NOT(ISBLANK([Last Name]@row)), IF(COUNTIFS(BG@row:[Lic/Cert]@row, "") = 7, "Not Started", IF((COUNTIFS(BG@row:[Lic/Cert]@row, "Green") + COUNTIFS(BG@row:[Lic/Cert]@row, "Gray")) = 7, "Complete", IF(NOT(ISBLANK([Did Not Start Reason]@row)), "No Start", "In Progress") ) ) )

Paul Newcome

I see the issue with the original formula. That closing parenthesis after the second 7 should have been moved to after "Gray". You closed the OR, but that second parenthesis needed to close the COUNTIFS.

kkinman

Thank you! It's working great now and appreciate all the help!

Paul Newcome

Happy to help. 👍️