PART A — Conditional and Aggregate Functions
QUESTION 1: Find the total Sales for each Region
THE STEPS:
1. Go to a new cell where you want the answer.
2. Type the start of the formula: =SUMIF(
3. Move your mouse to the top of your sheet and click the letter P
(this selects the entire Region column).
4. Type a comma: ,
5. Type the name of the region in quotes: "Central"
6. Type another comma: ,
7. Move your mouse to the top and click the letter R (this selects the
entire Sales column).
8. Type the closing bracket ) and hit Enter! ⌨️💥
THE FORMULA: =SUMIF(P:P, "Central", R:R) (Note: Just swap
"Central" for "South", "North", or "West" to get the others!)
THE ANSWERS:
o Central: $2,822,399
o South: $1,600,960
o North: $1,248,192
o Africa: $783,776
QUESTION 2: Find total Sales for each Region where Profit > 0 💰✅
THE STEPS:
1. Since we have two rules (Region AND Profit), we use the "S"
version! Type: =SUMIFS(
2. First, tell it what to add: Click letter R (Sales), then type a comma
,.
3. Second, give Rule #1: Click letter P (Region), type a comma ,, then
type "Central".
4. Third, give Rule #2: Type a comma ,, click letter N (Profit), type a
comma ,, then type ">0".
5. Close the bracket ) and hit Enter!
THE FORMULA: =SUMIFS(R:R, P:P, "Central", N:N, ">0")
THE ANSWERS:
o Central: $2,134,823
o North: $1,058,432
o South: $1,208,443
QUESTION 3: Compute total Sales for the Technology category in the Asia
Pacific market 🌏💻
THE STEPS:
1. Type: =SUMIFS(
2. Click letter R (Sales), then a comma ,.
3. Click letter A (Category), then a comma ,, then type
"Technology".
4. Click letter G (Market), then a comma ,, then type "APAC".
5. Close it ) and hit Enter!
THE FORMULA: =SUMIFS(R:R, A:A, "Technology", G:G, "APAC")
THE ANSWER: $1,357,498 💸
QUESTION 4: Count the number of orders placed in the Technology category
📦
THE STEPS:
1. Type: =COUNTIF(
2. Click letter A (Category column).
3. Type a comma , and then "Technology".
4. Close it ) and hit Enter!
THE FORMULA: =COUNTIF(A:A, "Technology")
THE ANSWER: 10,141 🤖
QUESTION 5: Find total Profit where Segment = Consumer and Discount <
0.2 📉
THE STEPS:
1. Type: =SUMIFS(
2. Click letter N (Profit), then a comma ,.
3. Click letter S (Segment), then a comma ,, then "Consumer".
4. Click letter F (Discount), then a comma ,, then "<0.2".
5. Close it ) and hit Enter.
THE FORMULA: =SUMIFS(N:N, S:S, "Consumer", F:F, "<0.2")
THE ANSWER: $1,110,899.45 🤑
QUESTION 6: Compute the average Profit for the Corporate segment 🏢⚖️
THE STEPS:
1. Type: =AVERAGEIF(
2. Click letter S (Segment), then a comma ,.
3. Type "Corporate", then a comma ,.
4. Click letter N (Profit).
5. Close it ) and hit Enter.
THE FORMULA: =AVERAGEIF(S:S, "Corporate", N:N)
THE ANSWER: $28.60 📈
PART B — Logical Functions and Nested IF
(IF, Nested IF, AND, OR)
1. Performance Rating
Create a new column Performance Rating using the following conditions:
Profit Range Rating
Profit > 500 Excellent
100 – 500 Good
0 – 100 Average
Profit < 0 Loss
2. Logical Classification
a) Mark “Premium Order” if:
Sales > 800 AND Quantity ≥ 5.
b) Flag “Danger Zone” if:
Discount ≥ 0.5 OR Profit < −100.
c) Create Shipping Priority:
Technology category with high sales → Express
Otherwise → Standard
PART C — Lookup, Sorting, Filtering and Conditional Formatting
(VLOOKUP, Sorting, Filtering)
Fetch Commission % using the following Sales slab (Approximate Match):
Sales Range Commission
0 – 200 2%
200 – 500 5%
500 – 1000 8%
Above 1000 10%
Data Analysis Tasks
1. Sort the dataset by Profit (Largest to Smallest).
2. Filter only Loss-making orders.
3. Find the Top 10 customers based on Sales.
4. Identify the Top 15 profitable customers.
5. Find the Bottom 10 loss-making cities.
6. Highlight Sales values greater than 1000.
7. Highlight rows where Discount ≥ 0.5.
8. Highlight Quantity values less than 3.
9. Automatically highlight duplicate Customer Names.
PART D — Pivot Tables and Data Visualization
1. Compute Average Profit by Segment.
2. Calculate Profit Margin (%) by Category.
3. Show Total Sales by Region and Segment.
4. Analyse Monthly Sales trend across years.
5. Show Profit contribution (%) of each Region.
Charts
1. Create a Column Chart comparing Category Sales.
2. Create a Line Chart showing Monthly Sales trend.
3. Create a Pie Chart showing Segment contribution.