How to Create a SharePoint Calculated Column Based on Two Choice Columns
Question details
The user needs a SharePoint calculated column that outputs specific text values (Critical, High, Moderate, Low, Lowest) based on the combined selections of two distinct choice columns.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating a priority or status tracker in a SharePoint list using multiple criteria.
- Observed behavior
- A calculated column is required to evaluate combinations of High, Medium, and Low inputs to return a consolidated priority level.
Ensure you have the necessary permissions to edit list settings in SharePoint and that both choice columns are already created and populated in your list.
Use Nested IF and AND Formulas for Calculated Columns
Create a calculated column that evaluates the values of two existing choice columns using logical functions to determine the correct output.
SharePoint uses standard Excel-like functions for its calculated columns. By combining the IF and AND functions, you can check multiple conditions simultaneously and return a specific text string for each matched combination.
Open your SharePoint list, click the Gear icon in the top right corner, and select 'List settings' from the dropdown menu.
Scroll down to the Columns section at the bottom of the page and click the 'Create column' link.
Enter a name for your new column (e.g., 'Overall Priority') and select 'Calculated (calculation based on other columns)' as the data type.
In the Formula text box, input your nested IF and AND statement. For example: =IF(AND([Impact]="High", [Urgency]="High"), "Critical", IF(AND([Impact]="High", [Urgency]="Medium"), "High", "Low")). Expand this logic to cover all your specific combinations.
Under 'The data type returned from this formula is', choose 'Single line of text'. Click 'OK' to save the column and apply it to your list.
Analyze and Manage Exported List Data with WPS Office
While SharePoint handles online lists, processing exported data, writing complex formulas, and creating reports is seamless with WPS Office. WPS Spreadsheet fully supports advanced logic functions like nested IF and AND, providing a lightweight, highly compatible, and free solution for your data management needs.
- 1. Export List to Excel: In your SharePoint list, click 'Export to Excel' to download your list data securely.
- 2. Open with WPS Spreadsheet: Launch WPS Spreadsheet and open the downloaded data file.
- 3. Apply Logic Formulas: Use the familiar formula bar to test and apply your nested IF and AND logic directly on your desktop before adding it back to SharePoint.
Frequently Asked Questions
Why is my SharePoint calculated column returning a syntax error?
Syntax errors often occur due to missing commas, unmatched parentheses in nested IF functions, or spelling mismatches in column names. Ensure column references are correctly enclosed in brackets like [Column Name].
Can I use choice columns that allow multiple selections in a calculated column?
No, SharePoint calculated columns do not support Choice columns that have 'Allow multiple selections' enabled. You must use single-selection choice columns for calculations to work properly.
Does a calculated column update automatically if I change the choice column values?
Yes, whenever a list item is created or updated, the calculated column will automatically recalculate its value based on the current selections of the referenced choice columns.
