Home > Software engineering >  Exclude Empty Empty Cells From Multiple Filters Formula In Google Sheets
Exclude Empty Empty Cells From Multiple Filters Formula In Google Sheets

Time:04-17

With this formula, if any of the filters has no results, an empty cell is included in the column of results.
How do I exclude empty results so that does not show?

=UNIQUE({  
IFERROR(FILTER(Sheet1!$B$2:$B,Sheet1!$A$2:$A=D1));   
IFERROR(FILTER(Sheet2!$B$2:$B,Sheet2!$A$2:$A=D1));   
IFERROR(FILTER(Sheet3!$B$2:$B,Sheet3!$A$2:$A=D1))  
})  

e.g.

Results Sheet1
Results Sheet1
Results Sheet1
------------- empty cell from no results for sheet2
Results Sheet3
Results Sheet3
...

CodePudding user response:

Assuming D1 contains a string, try:

=UNIQUE (QUERY( {Sheet1!$A$2:$B; Sheet2!$A$2:$B; Sheet3!$A$2:$B}, "Select Col2 where Col1 = '"&D1&"' and Col2 <>''", 0))

If D1 is a number, use

where Col1 = "&D1&""

and see if that helps?

  • Related