Formula Errors

Excel Formula to Return the Highest-Priority Test Status

Nimra MalikNimra Malik Nov 13, 2026 869 views

Question details

Create an Excel formula that evaluates the test statuses in S17:S19 and returns the highest-priority matching status in P16.

Excel Formula to Return the Highest-Priority Test Status
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.
Before you start

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.

Solution 1Recommended

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.

1
Review the source statuses

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.

2
Enter the formula in P16

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!"))))))

3
Verify the priority order

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.

4
Choose the fallback behavior

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.

Use nested COUNTIF checks to return the highest-priority status
Localized Excel formula syntax: The formula uses English function names and commas. If your Excel installation uses localized function names or semicolon separators, translate IF and COUNTIF as needed and replace the argument separators. Keep the quoted status labels identical to the text stored in your cells.
Combine test statuses in WPS Spreadsheet

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. 1. Open the test workbook: Launch WPS Spreadsheet and use Menu > Open to select your Excel workbook, or create a blank spreadsheet.
  2. 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. 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. 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.
Combine multiple test results into one priority-based status.Use familiar IF and COUNTIF functions without macros.Open and save Microsoft Excel .xlsx and .xls files.Keep an explicit Unknown! fallback when no recognized status is present.
microsoft office alternative - wps office

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.