How to Return YES When Any Column Contains YES in Excel
Question details
The user needs an Excel formula to check multiple columns in a row and output "YES" if at least one cell contains "YES", and "NO" only if all cells contain "NO".

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Evaluating multiple columns of survey, inventory, or checklist data to flag rows where at least one specific text value ("YES") is present.
- Observed behavior
- The goal is to automatically output the correct conditional text string without relying on overly complex nested IF statements.
Ensure your data is organized in contiguous columns and identify the exact cell range (for example, B2 to K2) you need to evaluate before writing your formula.
Use the IF and COUNTIF Functions
This is the most efficient and scalable method. By combining IF with COUNTIF, you can evaluate a large, continuous range of columns without writing a long, complex formula.
The COUNTIF function counts the number of cells within a range that meet a single condition. By wrapping it in an IF function, you can output "YES" as soon as the count is greater than zero.
Click on the cell where you want the final YES or NO result to appear (e.g., cell L2).
Type the formula `=IF(COUNTIF($B2:$K2,"YES")>0,"YES","NO")` into the formula bar. Adjust the range `$B2:$K2` to match the exact columns you are evaluating.
Press the Enter key on your keyboard to generate the result for that specific row.
Click and drag the fill handle (the small square at the bottom-right corner of the cell) down the column to apply the formula to the rest of your data.

Use the IF and OR Functions
Alternatively, you can use the OR function inside an IF statement to check specific, non-contiguous cells individually.
Easily Apply IF and COUNTIF Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports Excel formulas including IF, COUNTIF, and OR. You can effortlessly manage and analyze your data using the same familiar functions for free.
- 1. Open your worksheet: Launch WPS Spreadsheet and open your workbook containing the YES/NO data.
- 2. Input the formula: Select your result cell and type `=IF(COUNTIF(B2:K2,"YES")>0,"YES","NO")`.
- 3. Generate the result: Press Enter to execute the calculation and view the result.
- 4. Apply to other rows: Double-click the fill handle in the bottom-right corner of the active cell to auto-fill the formula down the entire column.

Frequently Asked Questions
Does the COUNTIF function differentiate between uppercase and lowercase text?
No, the COUNTIF function in Excel is not case-sensitive. It will evaluate "yes", "YES", and "Yes" as identical matches.
How do I check for multiple different words instead of just YES?
You can add multiple COUNTIF functions together inside your IF statement. For example, `=IF(COUNTIF(B2:K2,"YES")+COUNTIF(B2:K2,"MAYBE")>0,"YES","NO")` will return YES if either word is found.
Why does my IF formula return a #NAME? error?
This error typically occurs if there is a typo in the function name (like typing 'COUNTF' instead of 'COUNTIF') or if your text strings are not enclosed in double quotes (e.g., using YES instead of "YES").




