Google Sheets – How to Filter Across Multiple Columns Based on Criteria

formulasgoogle sheetsgoogle-sheets-custom-functiongoogle-sheets-filterworksheet-function

I do not know how to explain this perfectly without a picture so please take a look at the image before reading.

FIG 1.

I am trying to filter the data in Fig 1 (Above). based on the headers and then if the numbered column within that header is not 0 or blank. However, I also need the data from column A to be included in this filter.

For example, Take a look at the header "Bike" in column H. I want to be able to filter the data from only column H and column I because its within the "Boat" header. Then, I also need the blank cells and cells with 0 to not be included in the filter.

In the end it should look like Fig. 2 (Below) after it is filtered. I have tried doing filter if, just filter, etc. and still have not been able to figure it out. If I have to change my format as well let me know.

FIG 2.

Thank you so much in advance!

Best Answer

Edit per OP's comment
(This works great if the rows have no spaces in them but my data does have extra blank rows in between the rows of data as well.)

=IFNA(QUERY({A3:I11},"select Col1, Col"&MATCH(B16,A2:I2,0)&", Col"&MATCH(B16,A2:I2,0)+1&" 
                     where Col"&MATCH(B16,A2:I2,0)&" !=0 and Col1 is not null"),"missing")

No problem. Using the above formula you can target and filter each and every column in the query. Keep any or all of them according to your needs.

You would need the use of the QUERY function. 

=QUERY({A4:A8,D4:E8},"select Col1, Col2, Col3 where Col2 !=0")

Query non-consecutive columns

Using a "helper" cell e.g. B11 try the following for a more dynamic result: 

=IFNA(QUERY({A3:I7},"select Col1, Col"&MATCH(B10,A2:I2,0)&", Col3 
                     where Col"&MATCH(B10,A2:I2,0)&" !=0"),"missing")

To completely automate the process, using Data validation, create a drop-down on cell B11, set criteria List from a range and pick the headers (B2:I2).

Query non-consecutive columns based on headers drop-down list

Functions used:

Related Topic