Filter Function Formula

[Glasgowsmile]

I have the following formula, I'm trying to get it to use the entire array range if the criteria in the if statement is met. The if statement is true but I can't get the 'include' part of the filter to work so it does the full range. Any suggestions?

=FILTER(Raw!$A$3:$H$1000,IF(AND(J2="",K2="",$J$50="",$J$51="",$J$52=""),Raw!A3:H1000=Raw!A3:H1000),"")

 

[Fluff]

That formula makes no sense to me, this part Raw!A3:H1000=Raw!A3:H1000 will always return true.
Can you please explain what you are trying to do.

 

[Glasgowsmile]

That formula makes no sense to me, this part Raw!A3:H1000=Raw!A3:H1000 will always return true.
Can you please explain what you are trying to do.

I was trying to get it to use the full range of the array if the if statement was true. I didn't know how to say that in the include part of the filter function though.

 

[Fluff]

What should happen if all the criteria are no true?

 

[Alex Blakenburg]

Surely you don't just want this.

=IF(AND(J2="",K2="",$J$50="",$J$51="",$J$52=""),Raw!A3:H1000,"")

 

[Glasgowsmile]

What should happen if all the criteria are no true?

There will be several other IF statements nested within this filter but I wanted to get this one working first.

The problem I ran into is that A3:H1000 doesn't always have data up to row 1000, it ends up being blank but doing "" as the [if_empty] within the filter isn't working so it's showing a bunch of 0's and strange dates. I was trying to correct that.

Similar to this:

=FILTER(Raw!$A$3:$H$1000,IF(J2="",1,(Raw!$F$3:$F$1000=J2))*(IF(K2="",1,(Raw!$H$3:$H$1000=K2))*IF($J$50="",1,(Raw!$A$3:$A$1000>=$J$50))*IF($J$51="",1,(Raw!$A$3:$A$1000<=$J$51))*IF($J$52="",1,(Raw!$C$3:$C$1000>=Date_Structure!$D$2)*(Raw!$C$3:$C$1000<=Date_Structure!$D$3))*IF($J$49="",1,(Raw!$C$3:$C$1000>$J$49))),"")

 

[Fluff]

Can you please explain in words, what you are trying to do.
It would also help if you could post some sample data along with expected results.

 

[Glasgowsmile]

Can you please explain in words, what you are trying to do.
It would also help if you could post some sample data along with expected results.

:D Yeah, I understand the confusion but I found a solution!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Alex Blakenburg]

Do you want to post your solution ?
This seems to work. I have only put in the first 2 of your conditions.

=FILTER(Raw!$A$3:$H$1000,
IF(J2="",ROW(Raw!$F$3:$F$1000)>1,(Raw!$F$3:$F$1000=J2))*
IF(K2="",ROW(Raw!$H$3:$H$1000)>1,(Raw!$H$3:$H$1000=K2)),
"")