Formula Errors

Excel Formula for Multiple Yes, No, and Dash Conditions

Elise Williams Oct 21, 2026 868 views

Question details

Creating a logical formula to evaluate four cells containing "Yes", "No", or "-" and output a single prioritized result based on specific conditions.

How to Create an Excel Formula for Multiple Yes, No, and Dash Conditions

Product: Excel
Device & OS: not provided
Scenario: A user needs to evaluate a row of four cells. The formula must return a dash if all values are dashes, return the single non-dash value if only one exists, and strictly prioritize "No" over "Yes" if multiple values are present.
Observed behavior: Automating the cell evaluation accurately without manual checking, ensuring a hierarchical return of "-", "No", or "Yes" based on the cell contents.

Before you start

Ensure your target data range contains the exact text values "Yes", "No", or "-" without any hidden leading or trailing spaces, as these will cause the formula to miscalculate.

Solution 1Recommended

Use Nested IF and COUNTIF Functions

This method uses COUNTIF to efficiently count occurrences of specific values within your range, allowing you to prioritize the output dynamically.

By nesting IF statements alongside COUNTIF, you can check conditions sequentially. The formula first verifies if all cells are dashes, then checks for isolated values, and finally prioritizes 'No' over 'Yes'.

Select the destination cell

Click on the cell where you want the final evaluated result to appear (for example, E2).

Enter the nested formula

Type or paste the following formula into the formula bar: =IF(COUNTIF(A2:D2,"-")=4,"-",IF(COUNTIF(B2:D2,"-")=3,A2,IF(COUNTIF(A2:D2,"No")>0,"No","Yes")))

Apply and fill down

Press Enter to execute the formula. Then, click and drag the fill handle at the bottom-right corner of cell E2 downwards to apply this logic to the remaining rows in your dataset.

How the sequence works: The formula checks the all-dash condition first. If false, it checks if B2:D2 are dashes to return A2 as the only non-dash value. Finally, it checks if any 'No' exists; if found, it returns 'No', otherwise defaulting to 'Yes'.

Solution 2

Use the IFS and Logical Functions

If you are using a modern version of Excel, the IFS function evaluates multiple conditions in order and returns a value for the very first TRUE condition.

Evaluate Complex Conditions Easily with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for all advanced logical functions, including nested IFs, IFS, and COUNTIF, allowing you to seamlessly process complex conditions and analyze your data.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data to be evaluated.
2. Select the result cell: Click on the empty cell at the end of your data row where the formula will be placed.
3. Insert the logical formula: Type the nested IF or IFS formula directly into the formula bar at the top of the interface.
4. Press Enter and auto-fill: Press Enter to instantly calculate the result, then drag the cell's fill handle down to apply the evaluation rule to all other rows.

100% compatible with Microsoft Excel formulas and formattingFully supports advanced logical functions like IFS and COUNTIFLightweight program size ensures fast data processingFree to download and use with an intuitive interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning an incorrect result?

Incorrect results usually occur if your data contains hidden spaces (e.g., "Yes " instead of "Yes"). Excel evaluates these as entirely different text strings. You can use the TRIM function or the Find and Replace tool to clean your data before applying the formula.

Is the COUNTIF function case-sensitive?

No, the COUNTIF function is not case-sensitive. It treats "yes", "YES", and "Yes" as the exact same value when evaluating your conditions.

Can I expand this formula to evaluate more than four columns?

Yes. To evaluate more columns, simply adjust the cell ranges in the COUNTIF function (for example, change A2:D2 to A2:F2) and update the exact number of expected dashes (e.g., change the count from 4 to 6) to match the new range size.

What if my version of Excel doesn't support the IFS function?

The IFS function is available in newer versions of Excel and WPS Office. If you are using an older version, use the nested IF and COUNTIF formula detailed in the first solution, as it is compatible with all versions.

Excel & Data Problems