Search code examples
google-sheetsfiltergoogle-sheets-formulacountifgoogle-query-language

FILTER with multiple criteria (and blank values) with different range sizes

I have a multi column list that I want to filter results based on up to 4 criteria. Using FILTER/COUNTIF, I can achieve the desired result, so long as all criteria have values and of course matches. If I don't enter all four search criteria, the formula breaks. I would like to be able to search based on 1, 2, 3, or all 4 criteria and narrow my results accordingly.

Here is my formula (and a link to my spreadsheet - [Copy of dataValidations!G2]):

=UNIQUE(FILTER(squads!D3:D&"|"&squads!E3:E&"|"&squads!F3:F&"|"&squads!G3:G&"|"&squads!B3:B,
COUNTIF('Squad Builder'!C8,squads!D3:D),
COUNTIF('Squad Builder'!C9,squads!E3:E),
COUNTIF('Squad Builder'!C10,squads!F3:F),
COUNTIF('Squad Builder'!C11,squads!G3:G)
))

I've attempted to add IF statements around the COUNTIF, but if it is false, the formula breaks because it only has 1 result causing the ranges to differ in size.

My search criteria is located in another tab, Squad Builder!C8:C11

Additional Information

I have another formula that performs the same function and works fine. The difference is that I'm trying to display the results in | delimited format with this new formula. If that can somehow be achieved in this formula, that would be great too.

This is just a snippet of several formulas nested in dataValidations!G2.

  UNIQUE(SORT(QUERY({squads!B3:B,squads!D3:D,squads!E3:E,squads!F3:F,squads!G3:G},"SELECT Col1 "&
  IF(LEN('Squad Builder'!C8),"WHERE Col2 = '"&'Squad Builder'!C8&"' ",)&
  IF(LEN('Squad Builder'!C9),"AND Col3 = '"&'Squad Builder'!C9&"' ",)&
  IF(LEN('Squad Builder'!C10),"AND Col4 = '"&'Squad Builder'!C10&"' ",)&
  IF(LEN('Squad Builder'!C11),"AND Col5 = '"&'Squad Builder'!C11&"' ",)
  ,0),1,TRUE))
Solution

  • try:

    =INDEX(UNIQUE(QUERY({squads!D3:G, 
 squads!D3:D&"|"&squads!E3:E&"|"&squads!F3:F&"|"&squads!G3:G&"|"&squads!B3:B}, 
 "select Col5
  where 1=1 "&
 IF('Squad Builder'!C8<>"",  " and Col1 ='"&'Squad Builder'!C8&"'", )&
 IF('Squad Builder'!C9<>"",  " and Col2 ='"&'Squad Builder'!C9&"'", )&
 IF('Squad Builder'!C10<>"", " and Col3 ='"&'Squad Builder'!C10&"'", )&
 IF('Squad Builder'!C11<>"", " and Col4 ='"&'Squad Builder'!C11&"'", ))))