How to Use an Excel IFS Formula to Return Task Status from Multiple Conditions
Question details
The user needs an Excel formula that evaluates multiple cell conditions and returns one task status, such as Off, Not Completed, In Progress, or Completed.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A user is building a task tracker and wants to classify a row based on Off values, blank cells, partially completed cells, and fully completed cells.
- Observed behavior
- The formula should return Off first when an Off value exists, Not Completed when all cells are blank, In Progress when some cells are blank or partially completed, and Completed when all needed cells are populated.
Open your worksheet in WPS Office Excel and identify the exact status range, for example B2:G2. Keep the IFS condition order in mind because Excel returns the result for the first TRUE condition and ignores later matches.
Use an IFS Formula with COUNTIF and COUNTBLANK
Use this formula when you need a single task status that checks Off, all blank, partially blank, and completed cells in the correct order.
Excel evaluates the IFS conditions sequentially, so the Off check must come first to override blank and completed values. The formula then distinguishes all blank from partially blank cells before treating every populated cell as completed.
Open your workbook in WPS Office Excel and click the cell where you want the task status to appear, such as H2.
Type =IFS(COUNTIF(B2:G2,"Off")>0,"Off",COUNTBLANK(B2:G2)=COLUMNS(B2:G2),"Not Completed",COUNTBLANK(B2:G2)>0,"In Progress",TRUE,"Completed") and press Enter.
Drag the fill handle from the formula cell down through the remaining task rows to apply the same logic.
Change a cell in B2:G2 to Off, clear a cell, or fill all cells and confirm the result changes to Off, In Progress, or Completed as expected.
Extend the Formula with Date-Based Overdue and Not Yet Due Statuses
Use this variation when your task tracker includes due dates and you also need overdue or not-yet-due statuses.
Create a Task Status Formula in WPS Office Excel
WPS Office Excel supports the same IFS, COUNTIF, and COUNTBLANK functions, so you can create a task status formula without switching software. Open your workbook, enter the formula, and copy it down to classify tasks automatically.
- 1. Open the workbook in WPS Office Excel: Launch WPS Office, open the task tracker workbook, and click the cell where the status result should appear.
- 2. Enter the IFS status formula: Type =IFS(COUNTIF(B2:G2,"Off")>0,"Off",COUNTBLANK(B2:G2)=COLUMNS(B2:G2),"Not Completed",COUNTBLANK(B2:G2)>0,"In Progress",TRUE,"Completed") and press Enter.
- 3. Copy the formula to other rows: Drag the fill handle from the formula cell down through the remaining task rows to apply it.
- 4. Change a sample value to confirm the result: Enter Off or clear a cell in the evaluated range and verify that the status changes to the expected value.
Frequently Asked Questions
Why does my IFS formula return In Progress when only some status cells are filled?
After the all-blank check, the condition COUNTBLANK(B2:G2)>0 matches any remaining blank cell. The formula treats that as partially completed before the final TRUE condition can return Completed.
How do I make Off override all other statuses?
Place the Off condition first: COUNTIF(B2:G2,"Off")>0. Because IFS returns the first TRUE condition, any Off value in the range stops evaluation and returns Off.
Can I add overdue and not-yet-due statuses to this formula?
Yes. Add an AND condition with a due date cell and TODAY(), for example AND(D2<TODAY(),COUNTBLANK(B2:G2)>0), and insert those date-based checks before the blank checks in IFS.
Will this formula work in WPS Office if it was created in Microsoft Excel?
Yes. WPS Office Excel supports IFS, COUNTIF, COUNTBLANK, and COLUMNS, and it opens .xlsx workbooks without converting formulas.
