Home > Software design >  google sheets - query to filter both row and column
google sheets - query to filter both row and column

Time:11-16

I have a table like this in Google Sheets:

A table of values with four columns: Year, Quarter, A Sales, B Sales

in CSV format:

Year,Quarter,A Sales,B Sales
2020,1,100,150
2020,2,200,175
2020,3,150,200
2020,4,250,250
2021,1,300,75
2021,2,250,25
2021,3,200,250
2021,4,50,350

I would like to specify a Column (either "A Sales" or "B Sales") and a Quarter (either 1, 2, 3, or 4), and then display only the specified column and only the rows which match the Quarter in my results.

For example, if I specify Column="B Sales" and Quarter="2", I would like to display this in the Result column:

Dropdown to Select Quarter and Column by name, and results

Quarter,Column,Result
2,B Sales,175
,,25

How can I accomplish this in Google Sheets?

CodePudding user response:

Given your criteria in G2 & H2 as shown, the following nested FILTER in I2 gives the required result:

=filter(filter(C2:D,C1:D1=H2),B2:B=G2)

Also doable using QUERY, albeit with a longer formula.

  • Related