Function Problems

How to Return YES When Any Column Contains YES in Excel

Muhammad TalhaMuhammad Talha Nov 3, 2026 870 views

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".

How to Return YES When Any Column Contains YES in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell where you want the final YES or NO result to appear (e.g., cell L2).

2
Enter the formula

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.

3
Apply the result

Press the Enter key on your keyboard to generate the result for that specific row.

4
Fill down the formula

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 COUNTIF Functions
Dynamic Range: Using the dollar sign ($) for the columns ($B2:$K2) ensures that the column references stay fixed if you copy the formula horizontally, while the row numbers update dynamically as you drag it down.
Data Analysis Made Easy

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. 1. Open your worksheet: Launch WPS Spreadsheet and open your workbook containing the YES/NO data.
  2. 2. Input the formula: Select your result cell and type `=IF(COUNTIF(B2:K2,"YES")>0,"YES","NO")`.
  3. 3. Generate the result: Press Enter to execute the calculation and view the result.
  4. 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.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in robust function library to quickly insert and troubleshoot complex formulas.Lightweight, fast, and completely free to use for daily data processing tasks.
microsoft office alternative - wps office

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