Keep rows with where
PremiumIn E1, use QUERY to show the Rep and Amount of every sale over 100.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Rep | Amount | |||
| 2 | North | Ann | 120 | |||
| 3 | South | Bo | 80 | |||
| 4 | North | Cy | 200 | |||
| 5 | West | Di | 50 | |||
| 6 | South | Ed | 150 | |||
| 7 | ||||||
| 8 |
Premium lesson
Unlock QUERY 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.
Next with premium:ARRAYFORMULASPLIT and JOIN
Need help?
Why this works
where keeps only the rows where the condition holds. C > 100 is true for 120, 200 and 150, so those three sales come back, under the header row. The clauses go in a fixed order: select first, then where.
Common mistake: Putting where before select. A query starts with select, and where comes after it.
The answer
- E1
=QUERY(1A1:C6,2"select B, C where C > 100")
In plain words: From 1the table in A1:C6, 2show the rep and amount of every sale over 100.