Math & trig › SUMIFS16 / 144

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

MediumFreeSUMIFS

SUMIFS: revenue by branch and product

Dimas is the sales supervisor of a bottled sambal (chili sauce) brand with three branches: Bandung, Bogor and Depok. His manager wants the August revenue for several branch and product combinations.

Task

Fill H2:H6 with the total revenue for the branch in column F and the product in column G, using SUMIFS. Write the formula in H2, then copy it down.

Cells to fillH2:H6

Required functionsSUMIFS

Expected result

Loading the expected result…

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

Hint 1
Unlike SUMIF, SUMIFS puts the range to add first: SUMIFS(sum_range, range1, criteria1, range2, criteria2).
Hint 2
Sum range = the Revenue column (D), first pair = the Branch column (B) with F2, second pair = the Product column (C) with G2.
Hint 3
Lock all three data ranges with $ (rows 2 to 13) so they do not shift when copied.

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:H6

=SUMIFS($D$2:$D$13,$B$2:$B$13,F2,$C$2:$C$13,G2)

Write it in H2, then fill down to H6.

Explanation

=SUMIFS($D$2:$D$13,$B$2:$B$13,F2,$C$2:$C$13,G2) adds only the rows where the branch matches and the product matches. All ranges must be the same height. A common mistake is using the SUMIF argument order (sum range last), which gives a wrong result. SUMIFS also works with a single condition, so many people simply use SUMIFS everywhere.

Starting worksheet: Sheet1
Row	A	B	C	D	F	G	H
1	Date	Branch	Product	Revenue	Branch	Product	Total revenue
2	03/08/2026	Bandung	Sambal Bawang	1,000,000	Bandung	Sambal Bawang
3	04/08/2026	Bogor	Sambal Matah	700,000	Bandung	Sambal Matah
4	05/08/2026	Depok	Sambal Ijo	780,000	Bogor	Sambal Bawang
5	06/08/2026	Bandung	Sambal Matah	504,000	Bogor	Sambal Matah
6	07/08/2026	Bandung	Sambal Bawang	875,000	Depok	Sambal Ijo
7	10/08/2026	Bogor	Sambal Bawang	550,000
8	11/08/2026	Depok	Sambal Bawang	675,000
9	12/08/2026	Bandung	Sambal Ijo	390,000
10	13/08/2026	Bogor	Sambal Matah	560,000
11	14/08/2026	Depok	Sambal Ijo	624,000
12	18/08/2026	Bandung	Sambal Matah	728,000
13	19/08/2026	Bogor	Sambal Bawang	775,000
14
15
16

Fill in the requested cell, then press Check answer.

Validation result