Excel: Use IFS and COUNTIF to Return a Status Across a Row
Question details
Create a formula that checks daily status cells across one row and returns Yes, No, or Absent.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to summarize status values in cells AB27:AH27 into one result based on a priority order.
- Observed behavior
- The result should return Yes when any cell contains yes, then No when any cell contains no, and then Absent when any cell contains absent.
Make sure the status entries in AB27:AH27 use consistent text such as yes, no, and absent. COUNTIF is not case-sensitive, but extra spaces in a cell can prevent an exact match.
Return Yes, No, or Absent by Priority Across the Row
Use this formula when Yes should take priority over No, and No should take priority over Absent.
The formula tests the row from left to right by condition priority, not by the position of the status in the row. If more than one status exists, it returns the first matching result in the formula.
Click the cell where you want the row summary to appear, such as AI27.
Type or paste: =IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent")
Press Enter to calculate the result. Drag the fill handle down if other rows use the same AB:AH status layout.
Return No as the Default When No Yes or Absent Value Exists
Use this version when you only need to check for Yes and Absent, and every other outcome should be No.
Summarize Daily Row Statuses with IFS and COUNTIF in WPS Spreadsheet
WPS Spreadsheet supports IFS, COUNTIF, and IFERROR formulas for summarizing daily status cells into a single result. Enter the formula in your summary column, then fill it down to evaluate each row consistently.
- 1. Open the worksheet in WPS Spreadsheet: Open the Excel workbook in WPS Office and select the sheet containing the AB27:AH27 daily-status range.
- 2. Select a summary cell: Click the cell beside the row, such as AI27, where the combined status should appear.
- 3. Enter the formula: In the formula bar, enter =IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent") and press Enter.
- 4. Apply the formula to other rows: Select the result cell and drag its fill handle down to copy the adjusted formula to additional rows.
Frequently Asked Questions
What happens if a row contains both yes and no?
The formula returns yes because the first IFS condition checks for yes. Reorder the conditions if you need a different priority.
Can I change the range from AB27:AH27 to another row range?
Yes. Replace AB27:AH27 in every COUNTIF function with your status range, such as C27:I27.
Why does the formula not recognize a status that looks correct?
Check for leading or trailing spaces, such as " yes" or "absent ". COUNTIF matches the text criterion exactly apart from letter case.
How can I return blank when none of the statuses is found?
Wrap the formula with IFERROR and use an empty string as the fallback: =IFERROR(IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent"),"")
Does COUNTIF distinguish between Yes and yes?
No. COUNTIF is case-insensitive, so yes, Yes, and YES are treated as the same matching status.
