Query function with multiple criteria

Question

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'")

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, Dec 5, 2017 at 15:55

pnuts · Accepted Answer · 2017-12-05 16:07:07Z

0

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

answered Dec 5, 2017 at 16:07

pnuts

17.4k5 gold badges52 silver badges103 bronze badges

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
Dec 6, 2017 at 11:41

Add a comment |

1 Answer 1