QUERY › QUERY55 / 67

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeQUERY

QUERY: starts with, and, or

Rina sells bottled sambal and takes orders from three channels. Each order code starts with its channel: WA (WhatsApp), IG (Instagram) or WEB (website). She wants to see social media orders (WA or IG) worth at least Rp100,000 so she can add a free sticker.

Task

In E1, write one QUERY formula over A1:C9 that shows all columns for orders whose code starts with WA or IG, and whose total is at least 100000, including the headers. Watch the parentheses.

Cells to fillE1:G5

Required functionsQUERY

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
starts with checks the beginning of a text, for example A starts with 'WA'.
Hint 2
and is evaluated before or, so group the two channel conditions in parentheses.
Hint 3
The shape is select * where (… or …) and C >= 100000.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

E1

=QUERY(A1:C9,"select * where (A starts with 'WA' or A starts with 'IG') and C >= 100000",1)

Explanation

The parentheses make QUERY evaluate "WA or IG" first, then combine it with the total condition. Without them, and runs first, so every WA order shows up, including WA-0912 at only Rp85,000. Gita's order (exactly 100,000) is included because the condition is >=. Like contains, starts with and ends with are case-sensitive.

Starting worksheet: Sheet1
Row	A	B	C
1	Code	Buyer	Total
2	WA-0912	Ayu	85,000
3	IG-0913	Bima	120,000
4	WEB-0914	Citra	150,000
5	WA-0915	Dimas	210,000
6	IG-0916	Eka	60,000
7	WEB-0917	Fajar	95,000
8	WA-0918	Gita	100,000
9	IG-0919	Hana	175,000
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result