Level 2 project: a quarterly report from month sheets
To finish Level 2 you build a report that many offices put together at the start of every quarter: revenue sits on three month sheets, the targets on a fourth, and on the Summary sheet you bring it all together.
You need: references to other sheets, SUMIF, SUM and IF. If any of these still feels shaky, start with the lesson Reference another sheet.
The situation
Sales management wants to know how the North region did in the first quarter and whether it met its target. The workbook has five sheets:
- Jan, Feb, Mar: a list of regions and revenue, with the monthly total in B5. The regions are in a different order each month because the lists come from different exports.
- Targets: the quarterly target per region.
- Summary: this is where the report goes. You fill in the yellow cells.
The project
Task Quarterly report for the North region. On each month sheet the regions are in a different order. In B2, C2 and D2, fetch North's revenue from Jan, Feb and Mar, add up the quarter in E2, write "yes" or "no" in F2 depending on whether the target on the Targets sheet is met, and in B6 work out the quarterly revenue of all regions from the monthly totals.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Quarter | Target met? | |
| 2 | North | ||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | All regions | ||||||
| 7 | |||||||
| 8 |
| A | B | C | |
|---|---|---|---|
| 1 | Region | Revenue | |
| 2 | South | 4200 | |
| 3 | North | 5100 | |
| 4 | West | 3900 | |
| 5 | Total | 13200 | |
| 6 | |||
| 7 |
| A | B | C | |
|---|---|---|---|
| 1 | Region | Revenue | |
| 2 | West | 4100 | |
| 3 | South | 4500 | |
| 4 | North | 4800 | |
| 5 | Total | 13400 | |
| 6 | |||
| 7 |
| A | B | C | |
|---|---|---|---|
| 1 | Region | Revenue | |
| 2 | North | 5600 | |
| 3 | West | 4300 | |
| 4 | South | 4700 | |
| 5 | Total | 14600 | |
| 6 | |||
| 7 |
| A | B | C | |
|---|---|---|---|
| 1 | Region | Quarterly target | |
| 2 | North | 15000 | |
| 3 | South | 14000 | |
| 4 | West | 12000 | |
| 5 | |||
| 6 |
What matters here
No fixed cells from other sheets
The obvious answer =Jan!B3 happens to give the right number for January. In February, though, North is in row 4, and in March in row 2. The next export with a different sort order would make the report wrong without anyone noticing. SUMIF finds the region itself and stays right, which is why the exercise is also checked against other data.
Fetch the target from the Targets sheet
In F2 you compare the quarterly total with the target. The target is on the Targets sheet, so fetch it there with SUMIF and use the comparison inside IF. Read it as: if E2 is at least North's target, then "yes", otherwise "no".
The 3D SUM for all regions
B6 does not need a list of regions: every month sheet has its total in B5. With Jan:Mar!B5 you add them in one step.
Helper calculations
As in Excel, you may use empty cells for your own intermediate steps, for example fetch the target into a helper cell and refer to it in F2. Only the results in the yellow cells are checked.
If it does not work
- The message names the first cell that is not right yet. Everything before it is correct.
- If C2 shows the January value, the formula still points to the Jan sheet instead of Feb.
- If F2 always says "yes", the formula may be comparing with another region's target.
FAQ
Can I use VLOOKUP instead of SUMIF?
Yes, =VLOOKUP(A2,Jan!A2:B4,2,FALSE) works just as well. You learn VLOOKUP in Level 3. SUMIF is enough here, and it even adds correctly if a region appears twice in an export.
How many points is the project worth?
A project counts three times as much as a single exercise, because you solve several steps together.