Aggregation & criteria › SUMIFS9 / 67

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

MediumFreeSUMIFS

SUMIFS: revenue per branch and flavor

Mr. Joko owns an iced tea franchise with three branches in Central Java. The daily reports of all branches go into one sheet, and every Monday he wants to see the revenue of a certain flavor at a certain branch.

Task

In H2:H4, add up the revenue for the branch (column F) and flavor (column G) pair on the same row, using SUMIFS. Write the formula in H2, then copy it down.

Cells to fillH2:H4

Required functionsSUMIFS

Expected result

Loading the expected result…

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

Hint 1
In SUMIFS, the range to add up comes first, followed by pairs of criteria range and criterion.
Hint 2
There are two conditions: column B equals F2 and column C equals G2. Lock the data ranges with $ so they do not shift.
Hint 3
Pattern: =SUMIFS($D$2:$D$13, $B$2:$B$13, 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:H4

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

Write it in H2, then fill down to H4.

Explanation

=SUMIFS($D$2:$D$13,$B$2:$B$13,F2,$C$2:$C$13,G2) only adds rows that meet both conditions at once, for example Solo + Lychee = 2,505,000. Mind the order: in SUMIF the sum range comes last, while in SUMIFS it comes first. Because the criteria point to F2 and G2, the summary can be changed without touching the formula. For a full summary of every branch and flavor at once, QUERY with group by or a pivot table is more practical.

Starting worksheet: Sheet1
Row	A	B	C	D	F	G	H
1	Date	Branch	Flavor	Revenue	Branch	Flavor	Revenue
2	01/09/2025	Solo	Original	1,250,000	Solo	Lychee
3	01/09/2025	Solo	Lychee	840,000	Semarang	Original
4	01/09/2025	Semarang	Original	1,530,000	Magelang	Lemon
5	01/09/2025	Magelang	Lemon	620,000
6	02/09/2025	Semarang	Lychee	910,000
7	02/09/2025	Solo	Lychee	760,000
8	02/09/2025	Magelang	Original	980,000
9	02/09/2025	Semarang	Original	1,410,000
10	03/09/2025	Solo	Original	1,180,000
11	03/09/2025	Magelang	Lemon	700,000
12	03/09/2025	Semarang	Lemon	560,000
13	03/09/2025	Solo	Lychee	905,000
14
15
16

Fill in the requested cell, then press Check answer.

Validation result