Count matching values in date range.

Marcodip

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!!!

Marcodip

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!

Stacy Meadows

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 ???

Marcodip

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.

Stacy Meadows

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 :)

Marcodip

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!