CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Leanna
Leanna

Reputation: 117

Query of two filtered ranges doesn't display any results, if one filtered range has no matches

I am trying to query two filtered ranges to get the combined average:

=QUERY({FILTER(Filtered Range #1);FILTER(Filtered Range #2)},  
"select avg(Col2)    
label avg(Col2)' ' ", 0)

This query works great, EXCEPT when either of these filtered ranges happens to not have any matches, in which case the whole query will return as #VALUE! without even querying the other range's matches.

Is there any way to get around this? I've tried incorporating IFERROR statements in various places. I've tried averaging two separate queries, but taking the average of two averages isn't ideal.

Here's my Example Worksheet.

Please see how since there are no matches for the FILTER Condition 1 in the 2018 sheet, the whole QUERY does not return, even though there are matches in the 2019 sheet.

Any help is appreciated! Thanks!

Upvotes: 2

Views: 79

Answers (1)

kishkin
kishkin

Reputation: 5325

Enclose FILTER(...) in IFNA(FILTER(...), SPLIT(REPT(",", N), ",", True, False)), where N is the number of columns FILTER should return.

This SPLIT will give you a dummy {"", "", ..., ""} - N columns of empty cells which could be easily ignored in QUERY.

It will look something like:

=QUERY(
  {
    IFNA(FILTER(Filtered Range #1), SPLIT(REPT(",", 2), ",", True, False));
    IFNA(FILTER(Filtered Range #2), SPLIT(REPT(",", 2), ",", True, False))
  },
  "select avg(Col2)
   where Col1 is not null
   label avg(Col2) ''",
  0
)

Upvotes: 1

Related Questions