Dynamic arrays › FILTER100 / 144

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

EasyFreeFILTER

FILTER: transactions above 1 million

The manager of an electronics store in Medan wants a list of customers who spent more than Rp1,000,000 for a priority member program. The list must update when the transaction data changes.

Task

In D2, write one FILTER formula that returns the name and amount (A2:B7) for transactions above 1,000,000. The result spills into D2:E4.

Cells to fillD2:E4

Required functionsFILTER

Expected result

Loading the expected result…

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

Hint 1
FILTER(array, include): the array is A2:B7 and include is a comparison for every row.
Hint 2
The condition is B2:B7>1000000. Type the number without thousands separators.
Hint 3
Write it in D2 only, do not copy it down: the result spreads on its own.

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

D2

=FILTER(A2:B7,B2:B7>1000000)

Explanation

=FILTER(A2:B7,B2:B7>1000000) returns every row whose condition is TRUE as a dynamic array. Fajar, at Rp990,000, is left out because he did not pass 1 million. If another cell blocks the spill area, Excel shows #SPILL!. If no row matches, the result is #CALC!; the third argument of FILTER can hold text such as "None".

Starting worksheet: Sheet1
Row	A	B	D	E
1	Customer	Amount	Customer	Amount
2	Andi	1,500,000
3	Fajar	990,000
4	Bela	800,000
5	Citra	2,200,000
6	Dodi	450,000
7	Eka	1,250,000
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result