Excel Formula to Return the Highest-Priority Test Status
Question details
Create an Excel formula that evaluates the test statuses in S17:S19 and returns the highest-priority matching status in P16.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Combine several test results into one overall status using this priority order: To do, In progress, Failed, Accepted by PO, Passed, and Not tested.
- Observed behavior
- The desired result is the first status present according to the defined priority order, regardless of its position in S17:S19.
Confirm that the source statuses are in S17:S19 and the output cell is P16. Use consistent status labels without extra spaces, and confirm that the priority order is To do, In progress, Failed, Accepted by PO, Passed, then Not tested.
Use nested COUNTIF checks to return the highest-priority status
Use this formula to check all six statuses in priority order and display the first matching status in P16.
COUNTIF checks whether a status appears anywhere in the source range. Nested IF functions evaluate those checks in priority order, so the result depends on the status priority rather than the order of the cells.
This version returns Unknown! only when none of the six recognized statuses is found. Blank or unrecognized entries do not override a recognized status elsewhere in the range.
Select cells S17:S19 in Excel and check that their values use the labels To do, In progress, Failed, Accepted by PO, Passed, or Not tested.
Select cell P16, paste this formula into the formula bar, and press Enter: =IF(COUNTIF($S$17:$S$19,"To do")>0,"To do",IF(COUNTIF($S$17:$S$19,"In progress")>0,"In progress",IF(COUNTIF($S$17:$S$19,"Failed")>0,"Failed",IF(COUNTIF($S$17:$S$19,"Accepted by PO")>0,"Accepted by PO",IF(COUNTIF($S$17:$S$19,"Passed")>0,"Passed",IF(COUNTIF($S$17:$S$19,"Not tested")>0,"Not tested","Unknown!"))))))
As a quick test, enter Passed in S17, Failed in S18, and In progress in S19. P16 should display In progress. Restore the intended test results after checking.
If your rules require Not tested whenever no recognized status is found, edit the formula in P16 and replace the final "Unknown!" with "Not tested". Keep Unknown! if you need to distinguish missing or unrecognized results from an explicit Not tested status.

Calculate the highest-priority test status in WPS Spreadsheet
WPS Spreadsheet supports IF and COUNTIF, so you can use the same priority-based formula to combine the statuses in S17:S19 and show the result in P16.
- 1. Open the test workbook: Launch WPS Spreadsheet and use Menu > Open to select your Excel workbook, or create a blank spreadsheet.
- 2. Check the status cells: Open the relevant worksheet and enter or verify the test statuses in S17:S19 using the six agreed status labels.
- 3. Insert the priority formula: Select P16 and enter: =IF(COUNTIF($S$17:$S$19,"To do")>0,"To do",IF(COUNTIF($S$17:$S$19,"In progress")>0,"In progress",IF(COUNTIF($S$17:$S$19,"Failed")>0,"Failed",IF(COUNTIF($S$17:$S$19,"Accepted by PO")>0,"Accepted by PO",IF(COUNTIF($S$17:$S$19,"Passed")>0,"Passed",IF(COUNTIF($S$17:$S$19,"Not tested")>0,"Not tested","Unknown!")))))). Press Enter to calculate the result.
- 4. Check and save the result: Change one source cell to a higher-priority status and confirm that P16 updates. Use Menu > Save As and choose Excel Workbook (.xlsx) if you need to share the workbook with Excel users.

Frequently Asked Questions
Does the formula return the first status listed in S17:S19?
No. It checks the entire range for each status in the formula's priority order. For example, To do in S19 takes priority over Passed in S17 because the To do check comes first.
How do I change which status has the highest priority?
Select P16 and edit the formula in the formula bar. Move the COUNTIF check and its matching return label for your preferred status to the outermost IF. Arrange the remaining checks in descending priority order.
What happens if a source cell is blank or contains an unexpected status?
The formula ignores entries that do not match a recognized status. It returns a recognized status if one exists elsewhere in S17:S19, or Unknown! if none exists. If any blank or invalid entry must force Unknown!, add a separate validation check before the priority checks; the fallback alone does not enforce that rule.
Why does Excel reject the formula when I paste it?
Your Excel locale may require semicolons instead of commas or localized names for IF and COUNTIF. Select P16 and adjust the formula syntax to match your installation. Do not translate the quoted status labels unless the source cells use those translated labels.
How do I use the formula for a different range of test results?
Select the result cell and replace every occurrence of $S$17:$S$19 with your actual source range, such as $S$17:$S$25. The dollar signs keep the range fixed if you copy the formula to another cell.




