Count matching values in date range.
Hello Gang,
I've been losing my sleep over this formula so I thought of asking here.
I need to determine whether a certain value is found in another sheet and if the date of that value if found within the date range of the matching value in the second sheet.
Example
Sheet 1 includes the following columns: DEP,ARR, DATE
Sheet 2 includes the following columns: CODE,DATE START, DATE END
The goal is to adda a column in Sheet 1 that checks whether DEP or ARR exists in {CODE}. If true, verify if its corresponding DATE falls between {START DATE} and {END DATE} included.
I thought of using a combination of COUNTIF but I didn't get anywhere so far.
Thank you!!!
Best Answer
-
Thanks for your help Stacy!
Your formula was good but only for a 1 time use (as in it assumed the schedule monitored could only come across a specific event once).
I've worked it out in the end like this:
COUNTIF({Code}, DEP@row) + COUNTIF({Code}, ARR@row)/2 + IFERROR(COUNTIFS(INDEX({Start Date}, MATCH(DEP@row, {Code})), <=[Date]@row, INDEX({End Date}, MATCH(DEP@row, {Code}), >=[Date]@row)), 0) + IFERROR(COUNTIFS(INDEX({Start Date}, MATCH(ARR@row, {Code})), <=[Date]@row, INDEX({End Date}, MATCH(ARR@row, {Code})), >=[Flight Date]@row), 0)
If the result of the above is >1.5 then I know that at least one of the code@row exists in the given event date.
Thanks for your help nevertheless. It did help me to move forward and solve it!
Answers
-
Let me get this right you are trying to check that (sheet a/1) DEP@row or ARR@row = {CODE} (sheet b/2) , if so it needs to make sure DATE (sheet a/1) falls between {Start Date}- {End Date} You want an additional column in sheet a/1 to verify if this is true or not ???
-
Hi Stacy, yes, exactly.
So the sheet 1 is a timetable.
The sheet 2 is a list of events I am monitoring, identified by a code (that matches DEP/ARR), a start date, and an end date.
I need to figure if at any point in the timetable I intersect an event in sheet2.
-
I have a work around that:
firstly the code column sheet 2 and the arr and dep column on sheet 1 needs to be the same style of columns ...
on Sheet 1/a:
add a Date & Code Checkbox column
On sheet 2/b:
Add a Date column
and a checkbox column
Formula on date column sheet 2:
=IFERROR(IF(COUNTIFS({ARR}, CODE@row) > 1, VLOOKUP(CODE@row, {ARR-DATE}, 3, false), VLOOKUP(CODE@row, {DEP-DATE}, 2, false)), "")
(On this formula make sure you pull the right column number through....)
This formula matches code to ARR or DEP and pulls the date that matches the code through.
Formula on CHECK column sheet 2:
=IF(COUNTIF(Date@row:Date@row, >=[START DATE]@row) > 0, 1, IF(COUNTIF(Date@row:Date@row, <=[END DATE]@row) > 0, 1, 0))
(We have already matched code & pulled through date with Formula 1) So this formula checks if the date is between start and end date.
Formula on Date & Code Match sheet 1:
=IFERROR(IF(COUNTIFS({CODE1}, ARR@row) > 0, VLOOKUP(ARR@row, {Check 1}, 5, false), VLOOKUP(DEP@row, {Check 1}, 5, false)), "") (On the above formula make sure you pull the right column number through.)
This formula looks up code and pulls through the checkbox matches from sheet 2
Note: You can convert any of these formulas to column formula for an automatic process
Hope this helps :)
-
Thanks for your help Stacy!
Your formula was good but only for a 1 time use (as in it assumed the schedule monitored could only come across a specific event once).
I've worked it out in the end like this:
COUNTIF({Code}, DEP@row) + COUNTIF({Code}, ARR@row)/2 + IFERROR(COUNTIFS(INDEX({Start Date}, MATCH(DEP@row, {Code})), <=[Date]@row, INDEX({End Date}, MATCH(DEP@row, {Code}), >=[Date]@row)), 0) + IFERROR(COUNTIFS(INDEX({Start Date}, MATCH(ARR@row, {Code})), <=[Date]@row, INDEX({End Date}, MATCH(ARR@row, {Code})), >=[Flight Date]@row), 0)
If the result of the above is >1.5 then I know that at least one of the code@row exists in the given event date.
Thanks for your help nevertheless. It did help me to move forward and solve it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!