Excel IF Formula for TRUE, FALSE, and N/A Compliance Statuses
Question details
Create a formula that determines a compliance status from three TRUE, FALSE, or N/A values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user needs to evaluate three cells and return Defect if any value is FALSE, In Compliance if at least one value is TRUE and none are FALSE, or N/A if all three are N/A.
- Observed behavior
- The desired result is a single status that gives FALSE priority over TRUE and identifies an all-N/A combination.
Make sure the three source cells contain logical TRUE or FALSE values or the text N/A. The text N/A is different from the Excel error value #N/A.
Use IF and COUNTIF to classify the three values
Use this formula when each source cell contains TRUE, FALSE, or the text N/A.
The formula checks for FALSE first, so a single FALSE produces Defect even if another cell is TRUE. If there is no FALSE, at least one TRUE produces In Compliance; otherwise, the formula returns N/A.
In Excel, enter the three values to evaluate in cells A1, B1, and C1.
Select an empty cell, such as D1, and enter =IF(COUNTIF(A1:C1,FALSE)>0,"Defect",IF(COUNTIF(A1:C1,TRUE)>0,"In Compliance","N/A")).
Press Enter, then drag the fill handle from D1 down to evaluate the corresponding cells in additional rows.

Calculate Compliance Statuses in WPS Spreadsheet
Use the same IF and COUNTIF formula in WPS Spreadsheet to classify three values as In Compliance, Defect, or N/A.
- 1. Open the spreadsheet: Open WPS Spreadsheet and create a workbook or open an existing Excel file.
- 2. Enter the values: Place logical TRUE or FALSE values or the text N/A in cells A1 through C1.
- 3. Add the formula: Select D1 and enter =IF(COUNTIF(A1:C1,FALSE)>0,"Defect",IF(COUNTIF(A1:C1,TRUE)>0,"In Compliance","N/A")), then press Enter.
- 4. Evaluate more rows: Drag the fill handle down from D1 to apply the formula to additional rows.

Frequently Asked Questions
What happens if a row contains both TRUE and FALSE?
The formula returns Defect because it checks for FALSE before checking for TRUE.
What does the formula return for TRUE, N/A, and N/A?
It returns In Compliance because there is at least one TRUE and no FALSE.
Does the formula treat the #N/A error like the text N/A?
No. Enter N/A as text in the source cells; the Excel error #N/A is a different value and may cause an error in the calculation.
How do I use the formula for cells in different columns?
Replace both instances of A1:C1 in the formula with your three-cell range, such as E2:G2.




