FILTER with two conditions
PremiumIn E2, list the rep and sales of the North rows with sales over 300. Multiply the two tests so a row must pass both: (A2:A9="North")*(C2:C9>300).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | Rep | Sales | ||
| 2 | North | Ann | 420 | ||||
| 3 | South | Ben | 310 | ||||
| 4 | North | Cy | 150 | ||||
| 5 | West | Di | 500 | ||||
| 6 | South | Ed | 260 | ||||
| 7 | North | Fay | 380 | ||||
| 8 | North | Gus | 290 | ||||
| 9 | West | Hal | 340 | ||||
| 10 |
Premium lesson
Unlock FILTER and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
Why this works
Each test gives TRUE or FALSE per row. Multiplied, TRUE × TRUE is 1 and anything with a FALSE is 0, so only rows that pass both tests are kept: Ann with 420 and Fay with 380. Cy and Gus are North but under 300.
Common mistake: Using AND(A2:A9="North",C2:C9>300). AND squeezes the whole range into one TRUE or FALSE, so FILTER cannot use it row by row.
The answer
- E2
=FILTER(1B2:C9,2(A2:A9="North")*(C2:C9>300))
In plain words: Return 1the reps and sales in B2:C9 on the rows where 2the region is North and the sales are over 300.