Ignoring Blank fields on a Pass or Fail UAT sheet

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.

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 10/04/22

    Hi @David White

    I hope you're well and safe!

    Is there anything else in the range that could include "Fail"?

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Andree,

    Each one of the status fields could be classified as Pass, Fail or Blank, dependent on the number of steps in each test. No other columns use these nominated identifiers, they are free text for explanations on the test.

    I hope that helps?

    Thank you

    David

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @David White

    Happy to help!

    Would something like this work? (add another for Pass)

    =IF(COUNTIF([Step 1 - Status]@row:[Step 10 - Status]@row, "Fail") > 0, 1)

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks Andree,

    I am not sure how the countis would work, (my lack of understanding) and how we switch the 0.1 to pass fail?

    I was thinking more along the lines of:

    =IF((OR(ISBLANK([Step 10 - Status]@row), ISBLANK([Step 9 - Status]@row),ISBLANK([Step 8 - Status]@row)), AND([Step 10 - Status]@row = "Pass", [Step 9 - Status]@row = "Pass",[Step 8 - Status]@row = "Pass")), "Pass", "Fail")

    But this seems to be on parameter off too.

    Any ideas?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @David White

    You're more than welcome!

    I understand the confusion because I forgot to add the Fail or Pass result text.

    Try this.

    =IF(COUNTIF([Step 1 - Status]@row:[Step 11 - Status]@row, "Fail") > 0, "Fail", IF(COUNTIF([Step 1 - Status]@row:[Step 11 - Status]@row, "Pass") > 0, "Pass"))

    Did that work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • David White
    David White ✭✭
    Answer ✓

    Hi Andree,

    Yes that works perfectly, thank you. Hadnt thought of using Countif in this way. A good lesson!

    Thank you

    David

  • We have also developed this which works too!

    =IF((OR((OR(ISBLANK([Step 10 - Status]@row), ISBLANK([Step 9 - Status]@row), ISBLANK([Step 8 - Status]@row))))), "Pass", "Fail")

    Lots more complicated :)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @David White

    Excellent!

    You're more than welcome!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!