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.
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.