Compare three cells and the output is different pending on the answers in three cells
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am trying to put this together and all I can find is not helping me. I want to do the following:
- If A2, B2, or C2 have Yes and D2 has Yes, than Match
- If A2, B2, and C2 have No and D2 has No, than Match
- if anything else, No Match
I currently have it as:
=IF(and(A2=B2,B2=C2,C2=D2),"Match","No Match")
However, I need one to be an OR statement for Yes answers, and the above formula works only for the No answers. Is there way around this so that it is in one formula?
I'm using Office 2016 in Windows 10.
microsoft-excel worksheet-function microsoft-excel-2016
edited Feb 8 at 0:16
Scott
16.3k113990
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
add a comment |
I am trying to put this together and all I can find is not helping me. I want to do the following:
- If A2, B2, or C2 have Yes and D2 has Yes, than Match
- If A2, B2, and C2 have No and D2 has No, than Match
- if anything else, No Match
I currently have it as:
=IF(and(A2=B2,B2=C2,C2=D2),"Match","No Match")
However, I need one to be an OR statement for Yes answers, and the above formula works only for the No answers. Is there way around this so that it is in one formula?
I'm using Office 2016 in Windows 10.
microsoft-excel worksheet-function microsoft-excel-2016
edited Feb 8 at 0:16
Scott
16.3k113990
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
add a comment |
I am trying to put this together and all I can find is not helping me. I want to do the following:
- If A2, B2, or C2 have Yes and D2 has Yes, than Match
- If A2, B2, and C2 have No and D2 has No, than Match
- if anything else, No Match
I currently have it as:
=IF(and(A2=B2,B2=C2,C2=D2),"Match","No Match")
However, I need one to be an OR statement for Yes answers, and the above formula works only for the No answers. Is there way around this so that it is in one formula?
I'm using Office 2016 in Windows 10.
microsoft-excel worksheet-function microsoft-excel-2016
edited Feb 8 at 0:16
Scott
16.3k113990
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
I am trying to put this together and all I can find is not helping me. I want to do the following:
- If A2, B2, or C2 have Yes and D2 has Yes, than Match
- If A2, B2, and C2 have No and D2 has No, than Match
- if anything else, No Match
I currently have it as:
=IF(and(A2=B2,B2=C2,C2=D2),"Match","No Match")
However, I need one to be an OR statement for Yes answers, and the above formula works only for the No answers. Is there way around this so that it is in one formula?
I'm using Office 2016 in Windows 10.
microsoft-excel worksheet-function microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2016
edited Feb 8 at 0:16
Scott
16.3k113990
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
edited Feb 8 at 0:16
Scott
16.3k113990
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
edited Feb 8 at 0:16
Scott
16.3k113990
edited Feb 8 at 0:16
Scott
16.3k113990
edited Feb 8 at 0:16
Scott
16.3k113990
16.3k113990
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
asked Feb 7 at 22:46
KhalOmbreKhalOmbre
33
33
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
If at least one of A2, B2 or C2 must be "yes" when D2 is "yes", then try:
=IF(OR(COUNTIF(A2:D2,"no")=4,AND(COUNTIF(A2:C2,"yes")>0,D2="yes")),"match","nomatch")
The COUNTIF function will count the number of occurrences matching a certain condition.
If we want all four cells to be "no", then the count of cells whose value is "no" must be 4, hence COUNTIF(A2:D2,"no")=4.
If D2 = "yes", then we want at least one of the other cells to also be "yes", hence (COUNTIF(A2:C2,"yes")>0.
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
This helped solve my problem. Just so I understand, why is there a >4 and >0 in the formula? What is the purpose?
– KhalOmbre
Feb 7 at 23:23
Correction to my last comment: =4
– KhalOmbre
Feb 7 at 23:24
See the edit to my answer
– cybernetic.nomad
Feb 8 at 15:05
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1403337%2fcompare-three-cells-and-the-output-is-different-pending-on-the-answers-in-three%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If at least one of A2, B2 or C2 must be "yes" when D2 is "yes", then try:
=IF(OR(COUNTIF(A2:D2,"no")=4,AND(COUNTIF(A2:C2,"yes")>0,D2="yes")),"match","nomatch")
The COUNTIF function will count the number of occurrences matching a certain condition.
If we want all four cells to be "no", then the count of cells whose value is "no" must be 4, hence COUNTIF(A2:D2,"no")=4.
If D2 = "yes", then we want at least one of the other cells to also be "yes", hence (COUNTIF(A2:C2,"yes")>0.
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
This helped solve my problem. Just so I understand, why is there a >4 and >0 in the formula? What is the purpose?
– KhalOmbre
Feb 7 at 23:23
Correction to my last comment: =4
– KhalOmbre
Feb 7 at 23:24
See the edit to my answer
– cybernetic.nomad
Feb 8 at 15:05
add a comment |
If at least one of A2, B2 or C2 must be "yes" when D2 is "yes", then try:
=IF(OR(COUNTIF(A2:D2,"no")=4,AND(COUNTIF(A2:C2,"yes")>0,D2="yes")),"match","nomatch")
The COUNTIF function will count the number of occurrences matching a certain condition.
If we want all four cells to be "no", then the count of cells whose value is "no" must be 4, hence COUNTIF(A2:D2,"no")=4.
If D2 = "yes", then we want at least one of the other cells to also be "yes", hence (COUNTIF(A2:C2,"yes")>0.
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
This helped solve my problem. Just so I understand, why is there a >4 and >0 in the formula? What is the purpose?
– KhalOmbre
Feb 7 at 23:23
Correction to my last comment: =4
– KhalOmbre
Feb 7 at 23:24
See the edit to my answer
– cybernetic.nomad
Feb 8 at 15:05
add a comment |
If at least one of A2, B2 or C2 must be "yes" when D2 is "yes", then try:
=IF(OR(COUNTIF(A2:D2,"no")=4,AND(COUNTIF(A2:C2,"yes")>0,D2="yes")),"match","nomatch")
The COUNTIF function will count the number of occurrences matching a certain condition.
If we want all four cells to be "no", then the count of cells whose value is "no" must be 4, hence COUNTIF(A2:D2,"no")=4.
If D2 = "yes", then we want at least one of the other cells to also be "yes", hence (COUNTIF(A2:C2,"yes")>0.
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
If at least one of A2, B2 or C2 must be "yes" when D2 is "yes", then try:
=IF(OR(COUNTIF(A2:D2,"no")=4,AND(COUNTIF(A2:C2,"yes")>0,D2="yes")),"match","nomatch")
The COUNTIF function will count the number of occurrences matching a certain condition.
If we want all four cells to be "no", then the count of cells whose value is "no" must be 4, hence COUNTIF(A2:D2,"no")=4.
If D2 = "yes", then we want at least one of the other cells to also be "yes", hence (COUNTIF(A2:C2,"yes")>0.
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
edited Feb 8 at 15:05
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
answered Feb 7 at 22:59
cybernetic.nomadcybernetic.nomad
2,842617
2,842617
This helped solve my problem. Just so I understand, why is there a >4 and >0 in the formula? What is the purpose?
– KhalOmbre
Feb 7 at 23:23
Correction to my last comment: =4
– KhalOmbre
Feb 7 at 23:24
See the edit to my answer
– cybernetic.nomad
Feb 8 at 15:05
add a comment |
This helped solve my problem. Just so I understand, why is there a >4 and >0 in the formula? What is the purpose?
– KhalOmbre
Feb 7 at 23:23
Correction to my last comment: =4
– KhalOmbre
Feb 7 at 23:24
See the edit to my answer
– cybernetic.nomad
Feb 8 at 15:05
This helped solve my problem. Just so I understand, why is there a >4 and >0 in the formula? What is the purpose?
– KhalOmbre
Feb 7 at 23:23
This helped solve my problem. Just so I understand, why is there a >4 and >0 in the formula? What is the purpose?
– KhalOmbre
Feb 7 at 23:23
Correction to my last comment: =4
– KhalOmbre
Feb 7 at 23:24
Correction to my last comment: =4
– KhalOmbre
Feb 7 at 23:24
See the edit to my answer
– cybernetic.nomad
Feb 8 at 15:05
See the edit to my answer
– cybernetic.nomad
Feb 8 at 15:05
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1403337%2fcompare-three-cells-and-the-output-is-different-pending-on-the-answers-in-three%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
