Count how many times day was present in in overlapping ranges
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
Let's say I have some arbitrary number of date ranges in columns A and B (which can overlap), say:
A B
1 27.02.19 15.03.19
2 03.03.19 05.03.19
3 01.04.19 05.04.19
What I want to do is given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2, because that date was contained within the ranges in rows 1 and 2.
What formula can I use in Google Spreadsheets to calculate that? I realize COUNTIF is one part of this puzzle, but I need to 1) check all ranges in multiple rows and 2) check if the date is within the range, and I'm not sure how to do that.
google-sheets
migrated from superuser.com Mar 22 at 13:24
This question came from our site for computer enthusiasts and power users.
add a comment |
Let's say I have some arbitrary number of date ranges in columns A and B (which can overlap), say:
A B
1 27.02.19 15.03.19
2 03.03.19 05.03.19
3 01.04.19 05.04.19
What I want to do is given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2, because that date was contained within the ranges in rows 1 and 2.
What formula can I use in Google Spreadsheets to calculate that? I realize COUNTIF is one part of this puzzle, but I need to 1) check all ranges in multiple rows and 2) check if the date is within the range, and I'm not sure how to do that.
google-sheets
migrated from superuser.com Mar 22 at 13:24
This question came from our site for computer enthusiasts and power users.
I don't see that date in rows 1 and 2....
– Els den Iep
Mar 12 at 12:21
given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2 IfC1=04.03.19, then=COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1)
– Akina
Mar 12 at 12:56
@ElsdenIep 04.03.19 is included between the range given starting A1 and ending B1, as well as between A2 and B2.
– KaffeeKnollen
Mar 12 at 14:06
add a comment |
Let's say I have some arbitrary number of date ranges in columns A and B (which can overlap), say:
A B
1 27.02.19 15.03.19
2 03.03.19 05.03.19
3 01.04.19 05.04.19
What I want to do is given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2, because that date was contained within the ranges in rows 1 and 2.
What formula can I use in Google Spreadsheets to calculate that? I realize COUNTIF is one part of this puzzle, but I need to 1) check all ranges in multiple rows and 2) check if the date is within the range, and I'm not sure how to do that.
google-sheets
Let's say I have some arbitrary number of date ranges in columns A and B (which can overlap), say:
A B
1 27.02.19 15.03.19
2 03.03.19 05.03.19
3 01.04.19 05.04.19
What I want to do is given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2, because that date was contained within the ranges in rows 1 and 2.
What formula can I use in Google Spreadsheets to calculate that? I realize COUNTIF is one part of this puzzle, but I need to 1) check all ranges in multiple rows and 2) check if the date is within the range, and I'm not sure how to do that.
google-sheets
google-sheets
asked Mar 12 at 11:42
KaffeeKnollen
migrated from superuser.com Mar 22 at 13:24
This question came from our site for computer enthusiasts and power users.
migrated from superuser.com Mar 22 at 13:24
This question came from our site for computer enthusiasts and power users.
I don't see that date in rows 1 and 2....
– Els den Iep
Mar 12 at 12:21
given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2 IfC1=04.03.19, then=COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1)
– Akina
Mar 12 at 12:56
@ElsdenIep 04.03.19 is included between the range given starting A1 and ending B1, as well as between A2 and B2.
– KaffeeKnollen
Mar 12 at 14:06
add a comment |
I don't see that date in rows 1 and 2....
– Els den Iep
Mar 12 at 12:21
given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2 IfC1=04.03.19, then=COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1)
– Akina
Mar 12 at 12:56
@ElsdenIep 04.03.19 is included between the range given starting A1 and ending B1, as well as between A2 and B2.
– KaffeeKnollen
Mar 12 at 14:06
I don't see that date in rows 1 and 2....
– Els den Iep
Mar 12 at 12:21
I don't see that date in rows 1 and 2....
– Els den Iep
Mar 12 at 12:21
given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2 If
C1=04.03.19 , then =COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1)– Akina
Mar 12 at 12:56
given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2 If
C1=04.03.19 , then =COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1)– Akina
Mar 12 at 12:56
@ElsdenIep 04.03.19 is included between the range given starting A1 and ending B1, as well as between A2 and B2.
– KaffeeKnollen
Mar 12 at 14:06
@ElsdenIep 04.03.19 is included between the range given starting A1 and ending B1, as well as between A2 and B2.
– KaffeeKnollen
Mar 12 at 14:06
add a comment |
1 Answer
1
active
oldest
votes
For my solution you will need another column, based on the next formula:
E1 = 04.03.2019 # The Date you want to find
=If(AND($E$1>=A2;$E$1<=B2);True;False)
Now we count
=COUNTIF("C:C";True) # You can use "C2:C200" if that syntax is not allowed
I have done it this way because, you can check if the date is in the range compounded by two columns(Assuming the index column doesn´t exist, if it does the shift everything one column to the right) then you count the results.
I find really hard to make a oneliner out of this because you need a range in countif or countifs and most formulas return a value.
How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible
– KaffeeKnollen
Mar 12 at 14:21
Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The"C:C"inCOUNTIF()means the whole column. I did tested it.
– dmb
Mar 12 at 14:25
@KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question.
– dmb
Mar 12 at 14:28
You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that.
– KaffeeKnollen
Mar 12 at 14:34
@KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea.
– dmb
Mar 12 at 14:48
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "34"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
},
noCode: 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%2fwebapps.stackexchange.com%2fquestions%2f126648%2fcount-how-many-times-day-was-present-in-in-overlapping-ranges%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
For my solution you will need another column, based on the next formula:
E1 = 04.03.2019 # The Date you want to find
=If(AND($E$1>=A2;$E$1<=B2);True;False)
Now we count
=COUNTIF("C:C";True) # You can use "C2:C200" if that syntax is not allowed
I have done it this way because, you can check if the date is in the range compounded by two columns(Assuming the index column doesn´t exist, if it does the shift everything one column to the right) then you count the results.
I find really hard to make a oneliner out of this because you need a range in countif or countifs and most formulas return a value.
How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible
– KaffeeKnollen
Mar 12 at 14:21
Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The"C:C"inCOUNTIF()means the whole column. I did tested it.
– dmb
Mar 12 at 14:25
@KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question.
– dmb
Mar 12 at 14:28
You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that.
– KaffeeKnollen
Mar 12 at 14:34
@KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea.
– dmb
Mar 12 at 14:48
add a comment |
For my solution you will need another column, based on the next formula:
E1 = 04.03.2019 # The Date you want to find
=If(AND($E$1>=A2;$E$1<=B2);True;False)
Now we count
=COUNTIF("C:C";True) # You can use "C2:C200" if that syntax is not allowed
I have done it this way because, you can check if the date is in the range compounded by two columns(Assuming the index column doesn´t exist, if it does the shift everything one column to the right) then you count the results.
I find really hard to make a oneliner out of this because you need a range in countif or countifs and most formulas return a value.
How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible
– KaffeeKnollen
Mar 12 at 14:21
Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The"C:C"inCOUNTIF()means the whole column. I did tested it.
– dmb
Mar 12 at 14:25
@KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question.
– dmb
Mar 12 at 14:28
You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that.
– KaffeeKnollen
Mar 12 at 14:34
@KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea.
– dmb
Mar 12 at 14:48
add a comment |
For my solution you will need another column, based on the next formula:
E1 = 04.03.2019 # The Date you want to find
=If(AND($E$1>=A2;$E$1<=B2);True;False)
Now we count
=COUNTIF("C:C";True) # You can use "C2:C200" if that syntax is not allowed
I have done it this way because, you can check if the date is in the range compounded by two columns(Assuming the index column doesn´t exist, if it does the shift everything one column to the right) then you count the results.
I find really hard to make a oneliner out of this because you need a range in countif or countifs and most formulas return a value.
For my solution you will need another column, based on the next formula:
E1 = 04.03.2019 # The Date you want to find
=If(AND($E$1>=A2;$E$1<=B2);True;False)
Now we count
=COUNTIF("C:C";True) # You can use "C2:C200" if that syntax is not allowed
I have done it this way because, you can check if the date is in the range compounded by two columns(Assuming the index column doesn´t exist, if it does the shift everything one column to the right) then you count the results.
I find really hard to make a oneliner out of this because you need a range in countif or countifs and most formulas return a value.
answered Mar 12 at 13:45
dmb
How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible
– KaffeeKnollen
Mar 12 at 14:21
Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The"C:C"inCOUNTIF()means the whole column. I did tested it.
– dmb
Mar 12 at 14:25
@KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question.
– dmb
Mar 12 at 14:28
You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that.
– KaffeeKnollen
Mar 12 at 14:34
@KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea.
– dmb
Mar 12 at 14:48
add a comment |
How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible
– KaffeeKnollen
Mar 12 at 14:21
Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The"C:C"inCOUNTIF()means the whole column. I did tested it.
– dmb
Mar 12 at 14:25
@KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question.
– dmb
Mar 12 at 14:28
You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that.
– KaffeeKnollen
Mar 12 at 14:34
@KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea.
– dmb
Mar 12 at 14:48
How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible
– KaffeeKnollen
Mar 12 at 14:21
How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible
– KaffeeKnollen
Mar 12 at 14:21
Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The
"C:C" in COUNTIF() means the whole column. I did tested it.– dmb
Mar 12 at 14:25
Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The
"C:C" in COUNTIF() means the whole column. I did tested it.– dmb
Mar 12 at 14:25
@KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question.
– dmb
Mar 12 at 14:28
@KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question.
– dmb
Mar 12 at 14:28
You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that.
– KaffeeKnollen
Mar 12 at 14:34
You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that.
– KaffeeKnollen
Mar 12 at 14:34
@KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea.
– dmb
Mar 12 at 14:48
@KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea.
– dmb
Mar 12 at 14:48
add a comment |
Thanks for contributing an answer to Web Applications Stack Exchange!
- 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%2fwebapps.stackexchange.com%2fquestions%2f126648%2fcount-how-many-times-day-was-present-in-in-overlapping-ranges%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
I don't see that date in rows 1 and 2....
– Els den Iep
Mar 12 at 12:21
given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2 If
C1=04.03.19, then=COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1)– Akina
Mar 12 at 12:56
@ElsdenIep 04.03.19 is included between the range given starting A1 and ending B1, as well as between A2 and B2.
– KaffeeKnollen
Mar 12 at 14:06