Forum Discussion

B-Love's avatar
B-Love
Occasional Reader
Apr 30, 2026
Solved

Triple nested if statement fails on different column

I'm working on an audit template. Auditors are texting websites against a standard. The standard is listed on a sheet named 'Formula Values' and a column in the 'Findings' sheet has a drop-down drive...
excel
Formulas and Functions
  • Olufemi7's avatar
    Olufemi7
    Apr 30, 2026

    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.

B-Love's avatar
B-Love
Occasional Reader
Apr 30, 2026

Sorry, here is the actual formula I'm 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","")

))