Exercise 2: Analytical Formulas, Conditional Aggregations & Lookups

🎯Objective

Build multi-condition summaries using SUMIFS and COUNTIFS, construct defensive lookups with XLOOKUP, and clean messy text inputs.

Tasks & Challenges

**Level 1 (Recall)**: Explain why absolute cell referencing (`$`) is necessary when copying formula `=SUM(B$2:B2)` down a running total column. ✅ 2026-09-29

**Level 2 (Application)**: Write a single `SUMIFS` formula to calculate total sales for `Product == "Laptop"` sold in `Region == "East"`. ✅ 2026-09-29

**Level 3 (Lookup)**: Using `XLOOKUP`, retrieve the `Employee Bonus %` from an external lookup table. Provide a fallback of `0.0%` if not found. ✅ 2026-09-29

**Level 4 (Text Cleaning)**: Clean customer names in column A containing irregular spacing and mixed casing using `=PROPER(TRIM(A2))`. ✅ 2026-09-29

**Level 5 (Date Math)**: Calculate employee tenure in completed years using `=DATEDIF(HireDate, TODAY(), "Y")`. ✅ 2026-09-29

Solutions available in Ex02 Solutions.

Solutions: Exercise 2 (Formulas & Lookups)

Task 2: SUMIFS

EXCEL

=SUMIFS(Sales[Total], Sales[Product], "Laptop", Sales[Region], "East")

Task 3: XLOOKUP with Fallback

EXCEL

=XLOOKUP(A2, BonusTable[EmpID], BonusTable[BonusPct], 0.0)

Task 4: Text Cleansing

EXCEL

=PROPER(TRIM(A2))

Task 5: Date Tenure

EXCEL

=DATEDIF(B2, TODAY(), "Y")