Function Problems

Excel IF Formula for TRUE, FALSE, and N/A Compliance Statuses

Emma BrownEmma Brown Nov 18, 2026 868 views

Question details

Create a formula that determines a compliance status from three TRUE, FALSE, or N/A values.

Excel IF Formula for TRUE, FALSE, and N/A Compliance Statuses
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the source values

In Excel, enter the three values to evaluate in cells A1, B1, and C1.

2
Enter the formula

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

3
Apply it to other rows

Press Enter, then drag the fill handle from D1 down to evaluate the corresponding cells in additional rows.

Use IF and COUNTIF to classify the three values
Check the input values: The final N/A result means all three cells are N/A only if every input is restricted to logical TRUE, logical FALSE, or the text N/A. Blank cells or other text can also lead to that result.
WPS Spreadsheet

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. 1. Open the spreadsheet: Open WPS Spreadsheet and create a workbook or open an existing Excel file.
  2. 2. Enter the values: Place logical TRUE or FALSE values or the text N/A in cells A1 through C1.
  3. 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. 4. Evaluate more rows: Drag the fill handle down from D1 to apply the formula to additional rows.
Classify compliance values with IF and COUNTIFOpen and edit Microsoft Excel XLSX filesFill the formula down to evaluate multiple rowsUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.