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

Tags:

Best Answer

  • Marcodip
    Marcodip
    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.

  • Stacy Meadows
    Stacy Meadows ✭✭✭
    edited 12/08/22

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!