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

  • 1
    The post was better with your sample data as text. An image isn't as accessible. You just need to format it as code.
    – ale
    Commented Dec 5, 2017 at 15:55

1 Answer 1


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
  • Thanks pnuts, that works well, just a couple of questions. 1. The raw data is on another tab (and the filtered data is on a separate tab, where I apply the formula), so I replaced ‘filter(A:F,’ with the data range “FILTERED!A2:F” but this stopped the formula from working, any ideas why? 2. Rather than the filter bringing back all the data if I only wanted data from columns A to C back would I replace “select *” with “select A, B, C”? Thanks.
    – Alan Allen
    Commented Dec 6, 2017 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.