Formula Parse Error with Filter Function in Google Sheets

0
votes

I am trying to use a formula that searches a sheet called All Data for 2 variables. One is the store name and the other is the identifier payment method. Only 2 options are available for payment method. The other thing is I want it to search for multiple locations and filter to one sheet.

A2 = Beverly B2 = Stoneham

When I use this formula I get a parse error. Any ideas on what the issue is with this? When I use just one filter it works. It seems to be when I add that other filter it errors out.

=filter('All Data'!A:K,('All Data'!$B:B=A2,'All Data'!$E:E="EGFL")+('All Data'!$B:B=B2,'All Data'!$E:E="EGFL"))

This works for pulling all data from the main sheet

=filter('All Data'!A:K,('All Data'!$B:B=B2)+('All Data'!$B:B=A2)+('All Data'!$B:B=C2)+('All Data'!$B:B=D2)+('All Data'!$B:B=E2)+('All Data'!$B:B=F2))

I have tried this

=filter('All Data'!A:K,('All Data'!$B:B=B2,'All Data'!$E:E="REPL")+('All Data'!$B:B=A2,'All Data'!$E:E="REPL")+('All Data'!$B:B=C2,'All Data'!$E:E="REPL")+('All Data'!$B:B=D2,'All Data'!$E:E="REPL")+('All Data'!$B:B=E2,'All Data'!$E:E="REPL")+('All Data'!$B:B=F2,'All Data'!$E:E="REPL"))
1
filtergoogle-sheetsgoogle-sheets-formulagoogle-sheets-query

1 Answers

0
votes

try it like this:

=QUERY({
 IFERROR(FILTER('All Data'!A:K, 'All Data'!B1:B=A2, 'All Data'!E1:E="EGFL"), 
 {"","","","","","","","","","",""}); 
 IFERROR(FILTER('All Data'!A:K, 'All Data'!B1:B=B2, 'All Data'!E1:E="EGFL"), 
 {"","","","","","","","","","",""})}, 
 "where Col1 is not null", 0)

0