How can I set a formula to return “X”, when 3 conditions are not matching?
-1
I'll try to be specific. In the attached reference trix (Google sheet), there are 3 sheets,
- Login Sheet- where an email address, logs in on a certain date
- Schedule- schedule of the email address, for a week
- Attendance- where I want the attendance to be marked automatically, based on the inputs on "Login Sheet"
So far, I've managed to do the following (with many help which I received here):
- Check if the email address is on Leave/Off (in the Schedule sheet) - Then return- Leave/Off
- Check if an email is logged in ("login" in the Login Sheet) on a particular date - Then return "Present"
Here is where I'm stuck and need help -
When an email is scheduled but not logged in - As of now it shows #N/A'no match' (refer to D5,D6 within Attendance), I want this to return "Absent"
Here is the formula I've used so far:
=Ifs(Vlookup($A2,Schedule!$A:$E,4,0)="Leave","Leave",Vlookup($A2,Schedule!$A:$E,4,0)="Off","Off",countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present")
The Reference Sheet
excel google-sheets excel-formula google-sheets-formula
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
add a comment |
-1
I'll try to be specific. In the attached reference trix (Google sheet), there are 3 sheets,
- Login Sheet- where an email address, logs in on a certain date
- Schedule- schedule of the email address, for a week
- Attendance- where I want the attendance to be marked automatically, based on the inputs on "Login Sheet"
So far, I've managed to do the following (with many help which I received here):
- Check if the email address is on Leave/Off (in the Schedule sheet) - Then return- Leave/Off
- Check if an email is logged in ("login" in the Login Sheet) on a particular date - Then return "Present"
Here is where I'm stuck and need help -
When an email is scheduled but not logged in - As of now it shows #N/A'no match' (refer to D5,D6 within Attendance), I want this to return "Absent"
Here is the formula I've used so far:
=Ifs(Vlookup($A2,Schedule!$A:$E,4,0)="Leave","Leave",Vlookup($A2,Schedule!$A:$E,4,0)="Off","Off",countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present")
The Reference Sheet
excel google-sheets excel-formula google-sheets-formula
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
add a comment |
-1
-1
-1
I'll try to be specific. In the attached reference trix (Google sheet), there are 3 sheets,
- Login Sheet- where an email address, logs in on a certain date
- Schedule- schedule of the email address, for a week
- Attendance- where I want the attendance to be marked automatically, based on the inputs on "Login Sheet"
So far, I've managed to do the following (with many help which I received here):
- Check if the email address is on Leave/Off (in the Schedule sheet) - Then return- Leave/Off
- Check if an email is logged in ("login" in the Login Sheet) on a particular date - Then return "Present"
Here is where I'm stuck and need help -
When an email is scheduled but not logged in - As of now it shows #N/A'no match' (refer to D5,D6 within Attendance), I want this to return "Absent"
Here is the formula I've used so far:
=Ifs(Vlookup($A2,Schedule!$A:$E,4,0)="Leave","Leave",Vlookup($A2,Schedule!$A:$E,4,0)="Off","Off",countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present")
The Reference Sheet
excel google-sheets excel-formula google-sheets-formula
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
I'll try to be specific. In the attached reference trix (Google sheet), there are 3 sheets,
- Login Sheet- where an email address, logs in on a certain date
- Schedule- schedule of the email address, for a week
- Attendance- where I want the attendance to be marked automatically, based on the inputs on "Login Sheet"
So far, I've managed to do the following (with many help which I received here):
- Check if the email address is on Leave/Off (in the Schedule sheet) - Then return- Leave/Off
- Check if an email is logged in ("login" in the Login Sheet) on a particular date - Then return "Present"
Here is where I'm stuck and need help -
When an email is scheduled but not logged in - As of now it shows #N/A'no match' (refer to D5,D6 within Attendance), I want this to return "Absent"
Here is the formula I've used so far:
=Ifs(Vlookup($A2,Schedule!$A:$E,4,0)="Leave","Leave",Vlookup($A2,Schedule!$A:$E,4,0)="Off","Off",countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present")
The Reference Sheet
excel google-sheets excel-formula google-sheets-formula
excel google-sheets excel-formula google-sheets-formula
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
edited Nov 25 '18 at 9:40
Rakesh Sahu
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
asked Nov 25 '18 at 8:32
Rakesh SahuRakesh Sahu
32
32
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
You can add another condition in your function to check for absence
replace your function from
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present"
)
To
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")=0,"Absent",
)
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
don't forget to mark it as answered and upvote. same goes to your previous post, you haven't marked it as answered. And upvoting is the best way to say thanks.
– usmanhaq
Nov 25 '18 at 9:57
Hi usmanhaq, thank you for the guidelines. I've now marked both of the questions as answered. And additionally, I've been upvoting the answers that helped, but it seems my vote is invisible, because of low rep. Thanks again, and have a great day!
– Rakesh Sahu
Nov 25 '18 at 10:11
You can earn reputation by marking the questions you have asked as answered and you will get reputation, on the other hand you can participate in the community and answer somone's question to earn a little more reputation, i think you can upvote at reputation of 15 or more.
– usmanhaq
Nov 25 '18 at 10:18
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53465854%2fhow-can-i-set-a-formula-to-return-x-when-3-conditions-are-not-matching%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
0
You can add another condition in your function to check for absence
replace your function from
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present"
)
To
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")=0,"Absent",
)
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
don't forget to mark it as answered and upvote. same goes to your previous post, you haven't marked it as answered. And upvoting is the best way to say thanks.
– usmanhaq
Nov 25 '18 at 9:57
Hi usmanhaq, thank you for the guidelines. I've now marked both of the questions as answered. And additionally, I've been upvoting the answers that helped, but it seems my vote is invisible, because of low rep. Thanks again, and have a great day!
– Rakesh Sahu
Nov 25 '18 at 10:11
You can earn reputation by marking the questions you have asked as answered and you will get reputation, on the other hand you can participate in the community and answer somone's question to earn a little more reputation, i think you can upvote at reputation of 15 or more.
– usmanhaq
Nov 25 '18 at 10:18
add a comment |
0
You can add another condition in your function to check for absence
replace your function from
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present"
)
To
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")=0,"Absent",
)
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
don't forget to mark it as answered and upvote. same goes to your previous post, you haven't marked it as answered. And upvoting is the best way to say thanks.
– usmanhaq
Nov 25 '18 at 9:57
Hi usmanhaq, thank you for the guidelines. I've now marked both of the questions as answered. And additionally, I've been upvoting the answers that helped, but it seems my vote is invisible, because of low rep. Thanks again, and have a great day!
– Rakesh Sahu
Nov 25 '18 at 10:11
You can earn reputation by marking the questions you have asked as answered and you will get reputation, on the other hand you can participate in the community and answer somone's question to earn a little more reputation, i think you can upvote at reputation of 15 or more.
– usmanhaq
Nov 25 '18 at 10:18
add a comment |
0
0
0
You can add another condition in your function to check for absence
replace your function from
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present"
)
To
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")=0,"Absent",
)
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
You can add another condition in your function to check for absence
replace your function from
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present"
)
To
=Ifs(Vlookup($A2,Roster!$A:$E,4,0)="Leave", "Leave",
Vlookup($A2,Roster!$A:$E,4,0)="Off", "Off",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")>0,"Present",
countifs('Login response'!$D:$D,D$1,'Login response'!$B:$B,$A2,'Login response'!$C:$C,"login")=0,"Absent",
)
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
answered Nov 25 '18 at 9:13
usmanhaqusmanhaq
1,113128
1,113128
don't forget to mark it as answered and upvote. same goes to your previous post, you haven't marked it as answered. And upvoting is the best way to say thanks.
– usmanhaq
Nov 25 '18 at 9:57
Hi usmanhaq, thank you for the guidelines. I've now marked both of the questions as answered. And additionally, I've been upvoting the answers that helped, but it seems my vote is invisible, because of low rep. Thanks again, and have a great day!
– Rakesh Sahu
Nov 25 '18 at 10:11
You can earn reputation by marking the questions you have asked as answered and you will get reputation, on the other hand you can participate in the community and answer somone's question to earn a little more reputation, i think you can upvote at reputation of 15 or more.
– usmanhaq
Nov 25 '18 at 10:18
add a comment |
don't forget to mark it as answered and upvote. same goes to your previous post, you haven't marked it as answered. And upvoting is the best way to say thanks.
– usmanhaq
Nov 25 '18 at 9:57
Hi usmanhaq, thank you for the guidelines. I've now marked both of the questions as answered. And additionally, I've been upvoting the answers that helped, but it seems my vote is invisible, because of low rep. Thanks again, and have a great day!
– Rakesh Sahu
Nov 25 '18 at 10:11
You can earn reputation by marking the questions you have asked as answered and you will get reputation, on the other hand you can participate in the community and answer somone's question to earn a little more reputation, i think you can upvote at reputation of 15 or more.
– usmanhaq
Nov 25 '18 at 10:18
don't forget to mark it as answered and upvote. same goes to your previous post, you haven't marked it as answered. And upvoting is the best way to say thanks.
– usmanhaq
Nov 25 '18 at 9:57
don't forget to mark it as answered and upvote. same goes to your previous post, you haven't marked it as answered. And upvoting is the best way to say thanks.
– usmanhaq
Nov 25 '18 at 9:57
Hi usmanhaq, thank you for the guidelines. I've now marked both of the questions as answered. And additionally, I've been upvoting the answers that helped, but it seems my vote is invisible, because of low rep. Thanks again, and have a great day!
– Rakesh Sahu
Nov 25 '18 at 10:11
Hi usmanhaq, thank you for the guidelines. I've now marked both of the questions as answered. And additionally, I've been upvoting the answers that helped, but it seems my vote is invisible, because of low rep. Thanks again, and have a great day!
– Rakesh Sahu
Nov 25 '18 at 10:11
You can earn reputation by marking the questions you have asked as answered and you will get reputation, on the other hand you can participate in the community and answer somone's question to earn a little more reputation, i think you can upvote at reputation of 15 or more.
– usmanhaq
Nov 25 '18 at 10:18
You can earn reputation by marking the questions you have asked as answered and you will get reputation, on the other hand you can participate in the community and answer somone's question to earn a little more reputation, i think you can upvote at reputation of 15 or more.
– usmanhaq
Nov 25 '18 at 10:18
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53465854%2fhow-can-i-set-a-formula-to-return-x-when-3-conditions-are-not-matching%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
