Ignoring Blank fields on a Pass or Fail UAT sheet
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
-
Hi Andree,
Yes that works perfectly, thank you. Hadnt thought of using Countif in this way. A good lesson!
Thank you
David
Answers
-
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
-
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?
-
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.
-
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 :)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!