3

enter image description here

I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T DATE that meets the same specified range.

This is my ignorant attempt at trying to do this but it, of course, creates an Error.

=FILTER(A2:D6,C2:C6 <B10+D2:D6,"Y",B2:B6<B10)

Apologies if this is too easy to be asking; I've be reading for over 3 hours and haven't found how to do this or if this can be done.

Thank you

0

2 Answers 2

0

Filters values in A2:A6 if either condition is true:

=FILTER(A2:A6,
   (B2:B6<B10)+ 
   ((C2:C6<B10)*(D2:D6="Y")))

In the FILTER function conditions:

  • + is equivalent to OR
  • * is equivalent to AND
-1

Instead of FILTER you can use the QUERY function as in this formula:

=QUERY(A2:D6,"SELECT A WHERE D='Y' AND C <= date '"&TEXT(B10,"yyyy-MM-dd")&"'")

By referencing the cell B10, you can change the date in it to any date you like and it will be picked in the formula.

Functions used:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.