Forum Discussion

TomGrey's avatar
TomGrey
Copper Contributor
Jun 11, 2024
Solved

Date range search and marking if theres a value

I am looking for the correct excel formula for the following problem:
- in the corresponding cell - marked in yellow in the image - you should mark with an x if there is a value in the given row (letter I on a green background) and that value falls within the date range marked in rows 5 and 6. It does not matter how many real values - green letter I - are in the given date range, just one is enough to be marked in the yellow column.

Thank you very much in advance for your help!
Tom

 

  • TomGrey 

     

    The formula for the AO column must look like this!

    =IF(COUNTIFS(3:3,">="&AO$6,3:3,"<="&AO$5,7:7,"I")>0,"x","")

    See also my enclosed example.

    The prerequisite is that the cells in row 3 are correct dates.

2 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    TomGrey 

     

    The formula for the AO column must look like this!

    =IF(COUNTIFS(3:3,">="&AO$6,3:3,"<="&AO$5,7:7,"I")>0,"x","")

    See also my enclosed example.

    The prerequisite is that the cells in row 3 are correct dates.

    • TomGrey's avatar
      TomGrey
      Copper Contributor
      dscheikey

      Thank you very much, working perfectly with a slight modification to $3$3,. Wonderful you saved me a tremendous amount of time! Thank you very much!

Resources