Lab 02 · profit.xlsx

Lab 02

profit.xlsx

Monthly income, expenses, and profit data for 2023–2024 across three sales people: Nassar, Khaled, and Hamad. Complete all the summary tables on the right side of the sheet using what you learned in Topic 02.

Lab file: profit.xlsx 2 tasks · 5 check sections
Lab

profit.xlsx

Monthly income, expenses, and profit data for 2023–2024 across three sales people. Complete all the summary tables on the right side of the sheet, then check your answers below.

Download profit.xlsx

Task 1 — Calculate the Profit Column

Column E (Profit) is empty. Fill it using Income minus Expenses.

= C2 − D2
💡
Table calculated column
If the data is a Table, type the formula in E2 and press Enter — Excel fills it for every row automatically.

Task 2 — Summary Tables

Total profit — SUM of the entire Profit column.
Profit per sales person — SUMIF on the Sales person column, Profit as sum range.
% of total — person's profit ÷ Total profit. Lock the total cell with $ (absolute reference from Topic 01).
Average Income, Max Expenses, Min Profits — AVERAGE, MAX, MIN.
Profit by year (2023 / 2024) — SUMIF on the year column.

Check your answers

Total Profit
ItemYour answer
Total profit
Profit per Sales Person
Sales PersonProfit% of Total
Nassar
Khaled
Hamad
Average / Max / Min
MetricYour answer
Average Income
Max Expenses
Min Profits
Profit by Year
YearProfit
2023
2024
Bonus SUMIFS — two conditions at once

The cross-table (Sales person × Year) requires matching two conditions simultaneously. SUMIF handles one — so we use SUMIFS.

= SUMIFS(sum_range, range1, criteria1, range2, criteria2)
sum_rangeProfit column
range1 / criteria1Sales person column + name
range2 / criteria2Year column + 2023 or 2024

Example — Nassar's profit in 2023:

= SUMIFS(profit[Profit], profit[Sales person], "Nassar", profit[year], 2023)

Use mixed references for the full grid — $A2 locks the name column, B$1 locks the year row — so one formula copies across all cells.

SUMIFS Cross-Table — Your Results
Sales Person20232024
Nassar
Khaled
Hamad
Back to Topic
Topic 02 — Advanced Formulas