Forum Discussion
Triple nested if statement fails on different column
HelloB-Love,
Your formula fails because the logic order makes the N A condition unreachable. The second IF checks COUNTIF equals zero and returns Passed for anything not found in Findings, so Excel never evaluates the N A condition.
Also COUNTIF should compare a range to a single cell, not a range to a range. Use B2 and fill down instead of B2 colon B92.
Correct formula
=IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2)>0,"N/A",IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2)>0,"Failed","Passed"))This checks N/A first then Failed and returns Passed as the default.
First, I should have written "test" instead of "text." Second, I've fiddled with the formula so much that the formula I put in the original post was not the one I'm asking about. Here is the corrected formula that I am asking about:
=IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2:B92)>0,"Failed", IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2:B92)=0,"Passed",
IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2:B92)>0,"N/A","")
))
HelloB-Love,
Your formula fails because the logic order makes the N A condition unreachable. The second IF checks COUNTIF equals zero and returns Passed for anything not found in Findings, so Excel never evaluates the N A condition.
Also COUNTIF should compare a range to a single cell, not a range to a range. Use B2 and fill down instead of B2 colon B92.
Correct formula
=IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2)>0,"N/A",IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2)>0,"Failed","Passed"))
This checks N/A first then Failed and returns Passed as the default.
Thanks Olufemi7!