This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
I wanted to find out if someone can assist me with the best formula for the sample table below. There are 4 scenarios and I want to be able to add a formula that I can copy down in excel that if any of the 6 situations occur from the below table, it will have the results under the should be column. For example, if Scenario 1 is yes but the rest of the scenarios are no then the answer should be yes, if Scenario 1 is No and the rest are dashes then the answer should be no, if Scenario 1 is a dash and all other scenarios are yes then the answer should be yes, or if Scenario 1 is a dash and all others scenarios do not have a yes then the result should be no, lastly if all are dashes then the result should be a dash. I want to have this formula that covers everything in one cell that I can copy down to all other cells and it give the correct results based on the 6 scenarios.
| Scenario 1 | Scenario 2 | Scenario 3 | Scenario 4 | Formula | Should be |
|---|---|---|---|---|---|
| Yes | - | - | - | Yes | |
| No | - | - | - | No | |
| - | Yes | Yes | Yes | Yes | |
| - | Yes | No | No | No | |
| - | Yes | Yes | No | No | |
| - | - | - | - | - |
Thank you,
Yve
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi,
Enter this formula in cell E2
=IF(COUNTIF(A2:D2,"-")=4,A2,IF(COUNTIF(B2:D2,"-")=3,A2,IF(COUNTIF(B2:D2,"No")>=1,"No","Yes")))
Hope this helps.
Hi Yve. Try this nested statement:
=IFS(
AND(A2="-",B2="-",C2="-",D2="-"),"-",
OR(A2="No",B2="No",C2="No",D2="No"),"No",
OR(A2="Yes",B2="Yes",C2="Yes",D2="Yes"),"Yes"
)
Because the IFS function returns the first true statement, can evaluate it accordingly.
This is handling Hyphens explicitly. Alternatively, you could ignore anything that is not a Yes or No by using error handling. This returns an empty string if there is not a Yes or a No anywhere in the row. The last double quotes could be changed from "" to "-" to make it return a hyphen.
=IFERROR(IFS(OR(A7="No",B7="No",C7="No",D7="No"),"No",OR(A7="Yes",B7="Yes",C7="Yes",D7="Yes"),"Yes"),"")
Hope that helps!
D•J•S