Match text in where
PremiumIn E1, use QUERY to show the Rep and Amount of every sale in the North region.
| 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
The query is text in double quotes, so text inside it goes in single quotes: A = 'North'. Unlike SUMIF, QUERY compares text case by case, so 'north' would find nothing.
Common mistake: Using double quotes around North. They end the query text; inside the query, use single quotes.
The answer
- E1
=QUERY(1A1:C6,2"select B, C where A = 'North'")
In plain words: From 1the table in A1:C6, 2show the rep and amount of every sale in North.