IF and Filter with multiple IF conditions + multiple Filter conditions (Google Sheet, w/o VBA)

[lostinexcel91]

Hello. I have 2 sheets in 1 google sheet file.

SO (sheet1) shows all the data.
Sheet 2 is where the formula will be.

SO! (sheet1) shows:
Column E: Company Name
Column F: Units
Column G: Qty
Column L: Status
Column M: DR#
Column W: concatenated F and M


Sheet 2 shows:
A1: company drop down menu
A2: service unit drop down menu
Row 3: Headers
A4: =IF($A$1<>"",(FILTER(SO!W:W,SEARCH("SU ",SO!W:W),SO!L:L="COMPLETE",SO!M:M<>"",SO!E:E=$A$1)),(FILTER(SO!W:W,SEARCH("SU ",SO!W:W),SO!L:L="COMPLETE",SO!M:M<>"")))

Above formula works but only with 1 filter which is company. I need one more condition which is the service unit (A2).

Given that these conditions are also met:
SO!L:L="COMPLETE"
SO!M:M<>""

What I need to happen is
1. IF A1 and and A2 is blank, return list of all items in SO!W:W that has DR# and status COMPLETE
2. If A1 has a value and A2 is blank, return list of items under A1 company in SO!W:W that has DR# and status COMPLETE
3. If A1 is blank and A2 has a value, return list of A2 service units in SO!W:W that has DR# and status COMPLETE
4. If A1 and A2 has a value, return list of items under A1 company with A2 service units in SO!W:W that has DR# and status COMPLETE

Please note that SO!W:W is concatenated SO!F:F and SO!M:M, while Sheet2!A2 is Service Unit name only and has no DR number.

I hope someone can help Thanks in advance.

 

[dwang]

I'm not sure that's possible to do with filter. Normaly when I do something like that, I use query, and dynamically build the select statement using a bunch of ifs statements connected by &. In the ifs statements, I'm checking the content of the dropdowns as well as the status of A1 according to your requirements above.