Ignoring Blank fields on a Pass or Fail UAT sheet

Options
David White
David White ✭✭
edited 10/04/22 in Formulas and Functions

Hi All,

I have a sheet i am building for user acceptance testing of a program. We have various differing numbers of steps per test, and need to combine these steps with an overall pass or fail.

As we have some tests that have Statuses that are blank i need my formula to ignore the blanks and "Pass" them, it would only fail if tester assigns a fail to one of these statuses.

image.png

As step 8 status is blank, the overall test status is coming back as a fail. Which is incorrect.

My formula is:

=IF(AND([Step 10 - Status]@row = "Pass", [Step 9 - Status]@row = "Pass", [Step 8 - Status]@row = "Pass", [Step 7 - Status]@row = "Pass", [Step 6 Status]@row = "Pass", [Step 5 - Status]@row = "Pass", [Step 4 - Status]@row = "Pass", [Step 3 - Status]@row = "Pass", [Step 2 - Status]@row = "Pass", [Step 1 - Status]@row = "Pass"), "Pass", "Fail")

I am wondering if i need to add in ISBLANK or is there a better neater way?

Thank you

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!