# Need a formula to display a final test result for multiple text values

Options
✭✭✭✭✭
edited 09/16/22

Hello,

I need help with creating a formula to calculate a "final test result" for the following scenario:

1) If any cell in the row is “FAIL”, then result is “FAIL”

2) If all cells in the row are “PASS”, then result is “PASS”

3) If one cell is in the row is “N/A” or “blank”, then result is “INCOMPLETE”

4) If all cells in the row are “blank”, then the result is “NOT TESTED”

--Lisa M.

• ✭✭✭✭✭
Options

I created 3 helper columns to accomplish this - Fail Count, Pass Count, and Blank Count

Fail Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, ="Fail")

Pass Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, ="Pass")

Blank Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, OR(ISBLANK(@cell), @cell = "N/A"))

These will give us numerical values for pass/fail/blank/n/a within the range of Tester 1 to Tester 3 (or whatever range you want).

For the Final Test Result column, I have two formulas depending on how exactly you want it to work.

First, if you want it to say fail even if one of the values is blank or "N/A":

=IF([Fail Count]@row > 0, "Fail", IF([Pass Count]@row = 3, "Pass", IF(AND([Blank Count]@row > 0, [Blank Count]@row < 3), "Incomplete", "Not Tested")))

Second, if you want it to say incomplete until all values are filled in, even if there is a fail:

=IF(AND([Blank Count]@row > 0, [Blank Count]@row < 3), "Incomplete", IF([Pass Count]@row = 3, "Pass", IF([Fail Count]@row > 0, "Fail", "Not Tested")))

• ✭✭✭✭✭
Options

I created 3 helper columns to accomplish this - Fail Count, Pass Count, and Blank Count

Fail Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, ="Fail")

Pass Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, ="Pass")

Blank Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, OR(ISBLANK(@cell), @cell = "N/A"))

These will give us numerical values for pass/fail/blank/n/a within the range of Tester 1 to Tester 3 (or whatever range you want).

For the Final Test Result column, I have two formulas depending on how exactly you want it to work.

First, if you want it to say fail even if one of the values is blank or "N/A":

=IF([Fail Count]@row > 0, "Fail", IF([Pass Count]@row = 3, "Pass", IF(AND([Blank Count]@row > 0, [Blank Count]@row < 3), "Incomplete", "Not Tested")))

Second, if you want it to say incomplete until all values are filled in, even if there is a fail:

=IF(AND([Blank Count]@row > 0, [Blank Count]@row < 3), "Incomplete", IF([Pass Count]@row = 3, "Pass", IF([Fail Count]@row > 0, "Fail", "Not Tested")))

• ✭✭✭✭✭
Options

Hello @ericncarr

Thank you so much!! This worked perfectly! I used the helper columns and then the First formula for "fail, even if one of the values is blank or N/A". You are a lifesaver! I appreciate your help and this community so much.

I am now working on sheet summaries to use in a report for a dashboard.

--Lisa

• ✭✭✭✭✭
Options

@Lisa Matthews You're very welcome! Agreed, I enjoy coming here to see all the interesting solutions to various problems people find...good luck with the sheet summaries.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!