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

Options
Lisa Matthews
Lisa Matthews ✭✭✭✭✭
edited 09/16/22 in Formulas and Functions

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”

Thank you, in advance for your help!

--Lisa M.

Best Answer

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Lisa Matthews,

    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")))

Answers

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Lisa Matthews,

    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")))

  • Lisa Matthews
    Lisa Matthews ✭✭✭✭✭
    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

  • ericncarr
    ericncarr ✭✭✭✭✭
    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!