Google Sheets Query – Query Function with Multiple Criteria

filtergoogle sheetsgoogle-sheets-query

I want to use the query function in Google Sheets to filter data from one tab (see data below) to another to meet the following criteria:

  • F – No. Order = greater than 1
  • C – Area = City
  • D – Color =if its black it gotta be higher than 1000 in column E (Value) and if grey it gotta be higher than 150 in E.

I've tried using the below formula but it's not working:

=QUERY(FILTERED!A:F,"SELECT A,B,C,D,E,F WHERE F>1 AND IF D = 'Black' AND E>1000 AND IF D = 'Grey' AND E>150 AND AD='City'")

sample data

Best Answer

Please try:

=query({filter(A:F,C:C="City",D:D="Black",E:E>1000,F:F>1);filter(A:F,C:C="City",D:D="Grey",E:E>150,F:F>1)},"select *")

if the required result in the source sheet is:

123457  Rodgers    City    Black   2500     2
123458  Leaf       City    Black   1100   150
123459  Russell    City    Grey    2000    20
Related Topic