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

Lisa Matthews

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.

ericncarr

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

ericncarr

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

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

@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.