Function Problems

Excel Formula to Return Status Based on Multiple Criteria

Nimra Malik Oct 12, 2026 869 views

Question details

The user needs a formula to evaluate a row of individual status values and return an overall project status based on specific priority rules.

How to Return a Status When Multiple Criteria Are Met in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking project or task statuses where an overall completion status must be dynamically determined from multiple sub-task statuses in a single row.
Observed behavior: The user wants to automate the overall status determination (Completed, In Progress, or Follow Up) based on the presence of specific keywords (WIP, Needed, Follow Up) across multiple columns.

Before you start

Ensure your data range is consistent and that your status keywords match exactly, as the COUNTIF function is sensitive to hidden trailing spaces or typos.

Solution 1Recommended

Use a Priority-Based IF and COUNTIF Formula

Combine the IF function with COUNTIF to check if specific status values exist in a row, prioritizing 'In Progress' and 'Follow Up' statuses over 'Completed'.

This method uses COUNTIF to search a range for specific text. By adding the results of two COUNTIF functions together, we can simulate an 'OR' condition. If the count is greater than zero, the IF statement triggers the corresponding status.

Select the result cell

Click on the cell where you want the overall status (e.g., cell E2) to be displayed.

Enter the nested IF formula

Type the formula: =IF(COUNTIF(A2:D2,"WIP")+COUNTIF(A2:D2,"Needed")>0,"In Progress",IF(COUNTIF(A2:D2,"Follow Up")>0,"Follow Up","Completed")). Adjust the range 'A2:D2' to match the columns containing your data.

Apply to remaining rows

Press Enter to execute the formula. Click the cell again, grab the fill handle in the bottom-right corner, and drag it down to apply the formula to the rest of your dataset.

Use a Priority-Based IF and COUNTIF Formula

Understanding the formula logic: The formula checks for 'WIP' or 'Needed' first. If found, it immediately returns 'In Progress'. If not, it checks for 'Follow Up'. If none of these priority conditions are met, it defaults to 'Completed'.

Solution 2

Use Helper Cells for Dynamic Criteria Updates

Reference external cells for your status criteria so you can easily update the condition keywords without modifying the formula directly.

Smart Spreadsheet Formulas

Easily Manage Status Formulas with WPS Spreadsheet

WPS Office provides a highly capable spreadsheet tool that seamlessly supports advanced IF and COUNTIF functions, helping you track your task statuses efficiently without complicated setups.

1. Open your data file: Launch WPS Spreadsheet and open your existing task tracking document.
2. Use the formula builder: Click on the target cell and click the 'Insert Function' (fx) button located next to the formula bar.
3. Build your logic: Search for the IF and COUNTIF functions to visually build your nested logic based on your criteria.
4. Apply across your dataset: Enter the criteria references and drag the fill handle to apply the automated status tracking to your entire dataset.

Fully compatible with Microsoft Excel formulas, functions, and formats (.xlsx, .xls).Built-in visual formula builder to easily construct complex nested IF functions without syntax errors.Lightweight software with a familiar, easy-to-use interface that runs smoothly on older devices.Completely free to download and use for your daily spreadsheet and data tracking tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I add more status criteria to the formula?

Yes, you can expand the logic by nesting additional IF statements or adding more COUNTIF conditions. Simply use a plus sign (+) between COUNTIF functions to create an 'OR' logic for new statuses.

Why is my COUNTIF formula returning the wrong status?

This usually happens if there are hidden spaces in your cells. Use the TRIM function or ensure your data does not have accidental trailing spaces (e.g., typing 'WIP ' instead of 'WIP').

Is there an alternative to nested IFs for many conditions?

If you have many conditions, you might want to use the IFS function (available in newer versions of spreadsheets) which simplifies the syntax by removing the need to repeatedly write 'IF' for every single condition.

Excel & Data Problems