Dynamic arrays › SORTBY104 / 144

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

MediumFreeSORTBY

SORTBY: sort by region, then revenue

Joko runs a ground coffee distributor in Lampung. For Monday's meeting, he wants the sales reps grouped by region, with the top earner of each region at the top. The revenue figures are left out because the list will be shared with the whole team.

Task

In E2, write one SORTBY formula that returns the name and region (A2:B9), sorted by region from A to Z, then by revenue (C2:C9) from the largest. The result spills into E2:F9.

Cells to fillE2:F9

Required functionsSORTBY

Expected result

Loading the expected result…

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

Hint 1
Unlike SORT, SORTBY can sort by a column that is not part of the output: SORTBY(array, by_array1, order1, by_array2, order2).
Hint 2
The first key is the region (B2:B9, order 1). The second key is revenue (C2:C9, order -1).
Hint 3
Pattern: =SORTBY(A2:B9, B2:B9, 1, …, …).

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

E2

=SORTBY(A2:B9,B2:B9,1,C2:C9,-1)

Explanation

=SORTBY(A2:B9,B2:B9,1,C2:C9,-1) sorts by region first; within the same region, revenue is compared from largest to smallest. The result is Bandar Lampung (Bayu, Dimas, Fajar), Metro (Nadia, Rina, Wulan), then Pringsewu (Yoga, Ayu). The revenue column drives the order without appearing in the output, which is what SORTBY can do and SORT cannot. Every sort-by range must be as tall as the array being sorted (8 rows); otherwise the result is #VALUE!.

Starting worksheet: Sheet1
Row	A	B	C	E	F
1	Sales	Region	Revenue	Sales	Region
2	Rina	Metro	42,500,000
3	Dimas	Bandar Lampung	58,000,000
4	Ayu	Pringsewu	31,200,000
5	Fajar	Bandar Lampung	47,300,000
6	Nadia	Metro	51,750,000
7	Yoga	Pringsewu	36,900,000
8	Bayu	Bandar Lampung	62,100,000
9	Wulan	Metro	39,800,000
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result