FILTER exclude results if a range contains any value/text

[ausswe]

Hi all, I have the following formula that works fine and depends on if the user chooses a team in $D$5 or not:

=IF($D$5="";TAKE(SORT(FILTER(Data[[PERIOD]:[SPECIAL]];(Data[TOTALT]>60)*(Data[PERIOD]=$B$6));$A$6;-1);$E$5);TAKE(SORT(FILTER(Data[[PERIOD]:[SPECIAL]];(Data[TOTALT]>60)*(Data[PERIOD]=$B$6)*(Data[TEAM]=$D$5));$A$6;-1);$E$5))

I'm trying to figure out a way to exclude results in the filter if the user states "EXCLUDE" in cell $F$5 and any text/value is found in column (Data[NOTES]). If $F$5 contains "INCLUDE" or is empty I want it to include all results. I have tried various options but so far with no luck and I'm not sure if I'm overcomplicating the final formula so thought I'd look for ideas.

Thanks in advance

 

[Peter_SSs]

Try this

=TAKE(SORT(FILTER(Data[[PERIOD]:[SPECIAL]],(Data[TOTALT]>60)*(Data[PERIOD]=$B$6)*IF($D$5="",1,Data[TEAM]=$D$5)*IF($F$5="EXCLUDE",Data[NOTES]="",1)),$A$6,-1),$E$5)

 

[ausswe]

Hi Peter - yes that seems to be working.
I'll do some more testing during the week.

Interesting use if IF in FILTER like that, haven't seen that before.

Thank you so much!

 

[Peter_SSs]

Hi Peter - yes that seems to be working.
I'll do some more testing during the week.

Interesting use if IF in FILTER like that, haven't seen that before.

Thank you so much!

You're welcome. Thanks for the follow-up.