Here s a summary of Excel tasks and
concepts from the Application to
Zambikes video:
TASKS MR. MURRAY ASSIGNED
Worksheet: Customers
1. Calculate Total Sales (Column C)
Use: SUMIF function
Logic: From the Sales worksheet:
Match each customer s ID (Column A)
with Sales sheet Column B.
Sum matching values from Sales sheet
Column E (sales amounts).
Formula Example:
=SUMIF(Sales!$B$4:$B$655, A4,
Sales!$E$4:$E$655)
Use absolute cell references (F4) for
ranges since they ll repeat for each
customer.
2. Apply Discounts (Column D)
Use: Nested IF function
Conditions:
If total sales > 50,000 "15%"
If total sales > 15,000 "10%"
Otherwise "" (no discount)
Formula Example:
=IF(C4>50000, "15%", IF(C4>15000,
"10%", ""))
3. Count Sales Below Threshold (Column
G)
Use: COUNTIF function to count how
many total sales are below a value in F4
Formula Example (G4):
=COUNTIF($C$4:$C$11, "<" & F4)
Use ampersand (&) to join comparison
with cell value.
4. Find Incremental Counts (Column H)
Show change between cumulative
counts
Formula:
H4: =G4
H5 onward: =G5 - H4
Then autofill down to H11.
5. Apply Conditional Formatting (Column
C)
Color-code based on total sales:
20,000 Green
5,000 Red
2,000 Yellow
Steps:
1. Go to Home > Conditional Formatting >
New Rule > Use a formula.
2. Use this logic (fix column C, not row):
Green:
=$C4>20000
Red:
=$C4>5000
Yellow:
=$C4>2000
3. Order matters highest value first to
avoid lower rules overriding higher ones.
4. Use Manage Rules to adjust or reorder.
KEY FUNCTIONS USED
Function Purpose
SUMIF Sum based on a condition
IF + nested Apply tiered discount logic
COUNTIF Count how many meet a
condition
Conditional Formatting Visualize based on
sales level
Let me know if you want a visual Excel
mock-up of this worksheet or a practice
template to try on your own!
