SharePoint Document Issues

SharePoint Calculated Column Formula for Yes, No, and Not Applicable

Ayan Masood Oct 21, 2026 868 views

Question details

The user needs a SharePoint calculated column formula that evaluates multiple columns, returning "Yes" if all columns contain "Yes" or "Not applicable", and "No" if any column contains "No".

SharePoint Calculated Column Formula for Yes, No, and Not Applicable

Product: SharePoint
Device & OS: not provided
Scenario: Configuring a calculated column in a SharePoint list to dynamically evaluate QA or compliance status across multiple data columns.
Observed behavior: The user struggled to correctly include "Not applicable" as an acceptable value in the IF statement, resulting in incorrect "No" outputs when "Not applicable" was selected.

Before you start

Ensure you have List Owner or Full Control permissions in your SharePoint site to create or edit calculated columns, and note down the exact internal names of the columns you plan to reference.

Solution 1Recommended

Use the OR Function to Test for the Unwanted Value

Instead of checking for both 'Yes' and 'Not applicable', simplify the logic by checking if any column contains the unwanted 'No' value.

By using the OR function to look specifically for the value 'No', you automatically treat all other values (including 'Yes' and 'Not applicable') as acceptable. This approach keeps your SharePoint formula clean, prevents calculation errors, and avoids complex nested IF statements.

Access List Settings

Navigate to your SharePoint list, click on the Settings gear icon in the top right corner, and select "List settings".

Create or Edit the Column

Scroll down to the Columns section. Click "Create column" to make a new one, or click an existing calculated column to edit it.

Select Data Type

Enter a name for your column and choose "Calculated (calculation based on other columns)" as the column type.

Input the Formula

In the Formula box, enter the following logic: =IF(OR([A]="No",[B]="No",[C]="No",[D]="No",[E]="No",[F]="No"),"No","Yes")

Customize and Save

Replace the placeholders [A] through [F] with your actual SharePoint column names. Select "Single line of text" for the returned data type, then click "OK" to save your changes.

Use the OR Function to Test for the Unwanted Value

Syntax Tip: Make sure to enclose your exact column names in square brackets (e.g., [QA Status]) and ensure the spelling of 'No' exactly matches the data in your list.

Free Microsoft Office alternative

Analyze List Data Offline with WPS Spreadsheet

While SharePoint handles list calculations online, you might need to export and analyze your compliance data or QA reports locally. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office, allowing you to use advanced formulas in spreadsheets completely free of charge.

1. Download WPS Office: Download and install WPS Office for free from the official website.
2. Import Data: Open WPS Spreadsheet and open your exported SharePoint list data (CSV or XLSX format).
3. Apply Formulas: Use the familiar formula bar to apply IF and OR logic to your datasets seamlessly.

Fully compatible with Microsoft Excel (.xlsx) formats.Supports complex IF, OR, and AND formulas for in-depth data analysis.Lightweight software with a familiar, easy-to-navigate interface.Completely free to use with seamless data migration from other Office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SharePoint calculated column show a syntax error?

Syntax errors often occur if you miss a comma or a closing parenthesis. Additionally, depending on your SharePoint regional settings, you may need to use semicolons (;) instead of commas (,) to separate formula arguments.

Can I use the AND function instead of OR for this SharePoint formula?

Yes, but the logic would be much longer. You would need to verify that every single column is either 'Yes' or 'Not applicable', which requires combining AND with multiple OR statements. Checking for 'No' directly is more efficient.

How do I reference a column name with spaces in a SharePoint formula?

If your column name contains spaces, you must enclose it in square brackets, such as [QA Status]. It is a best practice to use brackets for all column references to avoid unexpected errors.

Does a SharePoint calculated column update automatically?

Yes, the calculated column automatically recalculates and updates its value whenever an item in the SharePoint list is created or modified.

Document & File Issues