Database functions › DSUM132 / 144

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

EasyFreeDSUM

DSUM: total sales for one city

Rizky runs a network of phone credit and electricity token agents in West Java. He keeps a criteria range next to the table, so he only has to change the city in F2 to see another city's total sales.

Task

In H2, calculate the total of the Amount column for the city in the criteria range F1:F2, using DSUM. The database is A1:D11 (including headers).

Cells to fillH2

Required functionsDSUM

Expected result

Loading the expected result…

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

Hint 1
The shape is DSUM(database, field, criteria). Both the database and the criteria must include their header row.
Hint 2
field can be the column number within the database (Amount is the 4th column) or its header cell, D1.
Hint 3
Pattern: =DSUM(A1:D11, …, F1:F2).

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

H2

=DSUM(A1:D11,D1,F1:F2)

Explanation

=DSUM(A1:D11,D1,F1:F2) (or with field 4) adds up the Amount only for rows whose city is Bogor, giving Rp9,390,000. The header in F1 must match the database header exactly; otherwise the criterion does not line up with any column. The strength of database functions is that the criteria are ordinary cells. Add another column next to it (say Product) for an "and" condition, or a new row below for an "or" condition.

Starting worksheet: Sheet1
Row	A	B	C	D	F	H
1	Agent	City	Product	Amount	City	Total sales
2	Mawar Cell	Bogor	Airtime	1,250,000	Bogor
3	Melati Cell	Depok	Data plan	2,100,000
4	Kenanga Cell	Bogor	Power token	3,400,000
5	Anggrek Cell	Bekasi	Airtime	950,000
6	Mawar Cell	Bogor	Data plan	1,800,000
7	Dahlia Cell	Depok	Power token	2,750,000
8	Kenanga Cell	Bogor	Airtime	640,000
9	Anggrek Cell	Bekasi	Data plan	1,500,000
10	Melati Cell	Depok	Airtime	1,120,000
11	Kamboja Cell	Bogor	Power token	2,300,000
12
13
14

Fill in the requested cell, then press Check answer.

Validation result