Adding More Criteria..

[Austin Lang]

Hey all,

Yesterday, we created this formula and it works well. I now need to be able to filter the returned results by a date range. I have put the start date in D5 and end date in E5. I have tried several different approaches and can't seem to get it right. I have a feeling I am on the right track, but not putting it in the correct way. Any help is appreciated.

=IFERROR(FILTER(Form!$A$5:$X$3001,(ISNUMBER(SEARCH("Failed",Form!C5:C3001)))+(ISNUMBER(SEARCH("Aborted",Form!C5:C3001)))),"")

Thank you.

 

[hagia_sofia]

Hello, generally speaking (since there is no view on a sample of your data) you are looking for something like: ((range of dates>=D5)*(range of dates<=E5))

 

[DanteAmor]

Assuming the dates are in column D (set D5:D3001)
Try:

=IFERROR(LET(a,Form!C5:C3001,b,Form!D5:D3001,FILTER(Form!$A$5:$X$3001,(ISNUMBER(SEARCH("Failed",a))+ISNUMBER(SEARCH("Aborted",a)))*(b>=D5)*(b<=E5))),"")

 

[Austin Lang]

Hello, generally speaking (since there is no view on a sample of your data) you are looking for something like: ((range of dates>=D5)*(range of dates<=E5))

Not sure if this helps. The dates are on Form!V5:V3000. I have tried (Form!V5:V3000>=D5)*(Form!V5:V3000<=E5) and it hasn't worked. Something similar to this is what I get when I have searched around. However, no matter where I put this, it doesn't return correctly.

 

[DanteAmor]

The dates are on Form!V5:V3000

In your image, it looks like they are in W.
But follow the example, if they are not in V, then change the formula to W:

=IFERROR(LET(a,Form!C5:C3001,b,Form!V5:V3001,FILTER(Form!$A$5:$X$3001,(ISNUMBER(SEARCH("Failed",a))+ISNUMBER(SEARCH("Aborted",a)))*(b>=D5)*(b<=E5))),"")

Then try

=IFERROR(LET(a,Form!C5:C3001,b,Form!W5:W3001,FILTER(Form!$A$5:$X$3001,(ISNUMBER(SEARCH("Failed",a))+ISNUMBER(SEARCH("Aborted",a)))*(b>=D5)*(b<=E5))),"")

 

[Austin Lang]

In your image, it looks like they are in W.
But follow the example, if they are not in V, then change the formula to W:

The "Form" tab that has all of the washes on it has the dates on V:V. The picture is the summary report page that I am creating. They are W:W on this page. The formula does work though, so I very much appreciate your help!